CODEWITHSUNDEEP
sql-serverdatabasedatabase-designweb-applications
John D
John D

Reputation: 687

Is it OK to have DB columns for "display purposes"?

We have two tables that need fields to be displayed in the application in various pages/screens and on reports as CONCAT of separate fields (concat within the same table)

1) Customer-table: FirstName+' '+(first-character([MiddleName])+' ' if not-null)+LastName intended result: "John Doe" -or- "John M Doe"

(Note: similar situation with AddrLine1+AddrLine2+City+State+Zip+Zip4)

2) Vehicle-table: VYear+' '+VMake+' '+VModel+' : '+VName intended result: "2008 Ford F350 : Blue" -or- "2008 Ford F350 : #45"

I see two ways to do these types of display outputs.

a) Create server-side VB-function to properly format the display from the field-values (some fields may be null, remember)

b) Have a DB-column in each of these tables such as [Name4Display] and [VName4Display] that are updated appropriately with INSERT- and UPDATE-triggers.

Of course your comments are welcome and welcome additional solutions to this question.

Thanks...J.

Upvotes: 1

Views: 129

Answers (5)

Marek Blackshire
Marek Blackshire

Reputation: 1

I would strongly recommend creating a view instead of adding fields to the table with duplicate data. The problem that will come up, besides using more space, is that the duplicated data may not always be in sync with the fields that make it up. As an example, if I have the following info in a table:

FirstName: 'Dave',
LastName: 'Jones',
FullName: 'Dave Jones'

And I then update his last name to 'Johnson', I now have this in the table:

FirstName: 'Dave'
LastName: 'Johnson'
FullName: 'Dave Jones'

It would also be possible to set up a trigger that automatically fills in the FullName field with the results of the other two fields, but that adds complexity. By using the view you could have all of the logic to filter out null values and format it as you wish.

Edit: Check out SQL Server String Concatenation with Null for how to implement the logic to check for null values while working with your values.

Upvotes: 0

A.S.H
A.S.H

Reputation: 29342

"View" is definitely the clean solution.

Upvotes: 0

Brian Pressler
Brian Pressler

Reputation: 6713

You have a few options. Creating another data field is not recommended. One option is to create a view with the added field like:

create view vw_customer as

select *, 
    isnull(FirstName + ' ','') + isnull(left(MiddleName,1) + ' ','') + isnull(LastName,'') as FullName
from Customer

You could also just use this select query without a view in your application. You could also just build the string in your application as you mentioned.

Another option is to create add a calculated field to your table so it's always available. Like this:

CREATE TABLE #Customer(
    [CustomerID] int IDENTITY(1,1) NOT NULL,
    [FirstName] varchar(50) NOT NULL,
    [MiddleName] varchar(50) NOT NULL,
    [LastName] varchar(50) NOT NULL,
    [FullName] AS isnull(FirstName + ' ','') + isnull(left(MiddleName,1) + ' ','') + isnull(LastName,'')
) ON [PRIMARY]

Upvotes: 1

Cortright
Cortright

Reputation: 1174

If you're just wanting to display the formatted data, why not write a view that does what you want? There's no reason to actually store the formatted value; it's redundant and messy.

Upvotes: 3

Mohammad Mirzaeyan
Mohammad Mirzaeyan

Reputation: 870

i think it's not good to have a column just for display data , as you said you must have triggers or functions for update these fields , remember that these operatins make your code complex and a good code it's just simple

Upvotes: 0

Related Questions