Table architecture best practice

Question

I have a table where I am storing configurations for a tool I have. It has a ConfigID which is just an identity field, customer name, application name, then it has 18 well known fields (wellknownfield1,wellknownfield2,...,wellknownfield18) that I know what to put in based off another table values.

Now my problem comes in. I also need custom values. Currently I have a dumb solution of having customfieldname1, customfieldvalue1,...,customfieldname20, customfieldvalue20). Where the values have all the random values I need delimited by pipes. I am using a SQL Server Database. Anyone have any suggestions? Please comment if anything is unclear.

Are the 'wellKnownField's really suffixed with numbers? If so, it suggests there's something up with your design. — Clockwork-Muse, Jul 06 '12 at 15:28

score 3 · Accepted Answer · answered Jul 06 '12 at 14:38

3

Strictly speaking, you should not put groups of values in a column. It violates the first normal form of relational data. Create a separate table called Custom Data (Config_ID, CUSTOM_NAME, CUSTOM_DATA_VALUE, CUSTOM_DATA_TYPE) and store the custom values in it.

answered Jul 06 '12 at 14:38

srini.venigalla

5,137
1
18
29

... #twitch#... an EAV table, but probably the only real solution available for this use case. If at all possible, `CUSTOM_NAME` and `CUSTOM_DATA_TYPE` should be foreign keys to master-data tables. – Clockwork-Muse Jul 06 '12 at 15:27

score 0 · Answer 2 · answered Jul 06 '12 at 14:35

0

Use another table with a foreign key. Save there all the customfieldname values you need to save. Use the ConfigID as foreign key to reference the ConfigID on main table that has the extra custom value.

answered Jul 06 '12 at 14:35

Yaroslav

6,476
10
48
89

score 0 · Answer 3 · edited May 23 '17 at 10:32

There is a standard way to lay out database tables to make them easy to manage - called normalization. There are different levels of normalization - first normal form, second normal form, third normal form...and higher (above third normal form are somewhat esoteric, in my opinion).

Explanations of these definitions here :

Normalization in plain English

What are 1NF, 2NF and 3NF in database design?

It can seem quite abstract - but the point is to get rid of any ambiguity or duplication in your database, and prevent problems further down the line.

As srini.venigalla points out - your table doesn't meet the criteria for first normal form - that every row should have the same number of data, one per DB column. Again, it might seem an abstract rule - but it's there to prevent real world problems - like, how do I parse this column value? How do I know what the separator is? What if it doesn't have enough data points? What if there are extra columns, and what are their names? All of these problems go away if you stick to one value per column.

The same is true for second normal form and third normal form - they disallow repeated values / redudancy in your database, which prevents real world problems of getting your DB in an inconsistent state.

There is debate / trade-offs about how far to normalize your database - but making everything meet third normal form seems to be an acceptable rule of thumb for a beginner.

(this is my conclusion after having to write code workarounds for my own non-1NF and non-2NF database schema)

this is also a really good resource : http://stackoverflow.com/questions/621884/database-development-mistakes-made-by-application-developers/621891#621891 e.g. discussing 'too much' vs 'not enough' normalization — Graham Griffiths, Jul 25 '13 at 14:16

Table architecture best practice

3 Answers3