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

Question

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.

score 3 · Accepted Answer · answered May 21 '15 at 21:23

3

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.

answered May 21 '15 at 21:23

Cortright

1,164
6
19

Yes, you can bind controls to columns from a view or even the output of a stored procedure. – Cortright May 21 '15 at 23:11

score 1 · Answer 2 · answered May 21 '15 at 21:26

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]

score 0 · Answer 3 · answered May 21 '15 at 21:20

0

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

answered May 21 '15 at 21:20

Mohammad Mirzaeyan

845
3
11
30

score 0 · Answer 4 · edited May 23 '17 at 12:07

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.

I just noticed that you mentioned using the triggers as an option. I'd still recommend not doing that and stick with a view. — Marek Blackshire, May 21 '15 at 21:23

score 0 · Answer 5 · answered May 21 '15 at 21:26

0

"View" is definitely the clean solution.

answered May 21 '15 at 21:26

A.S.H

29,101
5
23
50

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

5 Answers5