CODEWITHSUNDEEP
databasefield
Michael Haddad
Michael Haddad

Reputation: 4435

When creating a database - Is it advisable to create a column for data I can calculate based on other fields?

I am building an application for a lawyer, in which he can create a client portfolio. In this portfolio, there is the ID of the portfolio, the creation date, the client's name, telephone, etc.

Besides of all these fields, there is another field: "portfolio name". This field contains some information about the client from the other fields, in a formatted text.

So, for example, if:

  1. ID = 271
  2. client_name = "John Doe"
  3. creation_date = 18/02/2016

the portfolio_name will be 271/John Doe/18022016.

Now, since the portifolio_name is not really containing new data, but only formatted data from other fields, should it really exist in the database table as a column? Is that Data Duplication or not?

Upvotes: 0

Views: 42

Answers (3)

Alex Howansky
Alex Howansky

Reputation: 53573

This is a textbook violation of 1NF and should generally be avoided. It's acceptable in some cases -- for example, where the calculated value is very difficult or time-consuming to obtain. However, since string concatenation is so simple (you can even do it right in your query, without the definition of any pseudo-fields) I wouldn't ever recommend doing this unless the field simply contains an initial default value and the client has the ability to customize it later. Otherwise, it will eventually become inconsistent. E.g., what happens when a client's name changes?

Upvotes: 1

Jesse Weigert
Jesse Weigert

Reputation: 4854

Most database engines allow you to create a computed column for this exact purpose. Depending on the engine and how you set up the computed column it may or may not be saved to disk, but it will be guaranteed to be always up to date. The nice thing is that you can treat it like it's a read only column.

https://technet.microsoft.com/en-us/library/ms191250%28v=sql.105%29.aspx

Upvotes: 1

ab0369
ab0369

Reputation: 206

It depends on the size of the table and how you query the table.

If the table is large you can create a column for the calculated field. So that it will be easy for querying.

If the table small you can calculate in the query

Upvotes: 1

Related Questions