Correct way to create a field/value table?

Question

I need help. I don't know what the right way to create the table I need is. I can do it different ways, but I might be doing it a stupid way.

Here is an example of the situation I have.

I have persons. Each person has fields.

Fields could be: ShirtType, PantType, HairColor, EyeColor, ShoeType, Height, Weight, etc. I will need to add additional fields in the future.

Each person may not use all fields. (Some people would only use one field).

Should I:

1. Create one table with all fields as columns, and just put data in the ones I need each time?

OR

1. Create a Fields table to hold all field names. Then, create a Values table to hold values, referenced by FieldId?

The first seems less confusing, but the second wastes less space. Are NULL fields (method 1) a waste of space? Bad design? I can easily import data into the first method, but the second one I would have to add a list of values and field id, one value list at a time. Could take a while.

I apologize if this doesn't make perfect sense, but I really could use some advice.

Thanks.

Answer 1

Best Practices for Semantic Data Modeling for Performance and Scalability

Answer 2

The "Type" fields could go into one of two types of tables--either a foreign keyed table or a "lookup" table. Where there are only a few possible values for a field, I tend to use lookup tables. They have fields like lookupType which could be "ShirtType", "PantType", etc that are used to select the correct rows. Then there are fields like lookupKey (unique, like an int, "primary key") and lookupValue (the value of said record, like "t-shirt" or "long sleeve")

Examples of records in this single table could be:

lookupType  lookupKey  lookupValue
Gender      1          Male
ShirtType   1          T-Shirt
Gender      2          Female
PantType    1          Shorts
ShirtType   2          Long Sleeve

So, your query for all the gender types looks like:

SELECT lookupKey, lookupValue
WHERE lookupType = 'Gender'

Answer 3

If you have a relatively small and knowable number of 'fields' - that is, you aren't going to think up more fields that need to be added later on - then the first way is acceptable.

But in other cases, where you don't know how many fields or there are a large number of fields, or it's likely that new fields with be added, then just add another table. It gives proper relational modelling and easier extensibility.

Write some code to transform your 'flat' records into the correct database structure for reuse and then inserts are just as simple as if it were in a single table.

Answer 4

If each person is only going to have ONE type of each, you could have it all in one table. If a person will have 2 or more of the same, (ie. 2 ShirtTypes) then you should consider having a table for the different fields, so you can do better searches.

I will make the example in case you need 2 or more of some type of field. You will also need tables to tell you what each value stands for. And the fields that you don't use, don't know the value yet or have just added, can have 0 on them (or NULL, but I would prefer 0):

Table: PersonCommonInfo Only place here what a person has only once and can NEVER have two or more

PersonId   Gender   Height   Weight   HairColor   EyeColor   SkinColor
1          M        55       200      1           1          0
2          M        60       220      7           0          0
3          F        50       130      0           4          0

Table: PersonToAccessories This table would contain any fields that a person can have 1 or more. If you are sure this will never be the case, you can merge it with the previous table and save some space. Though, in preparation for the future, it is better to have it like this to begin with.

PersonAccessoryId   PersonId   AccesoryType   AccesoryValue
1                   1          Shirt          1
2                   2          Shirt          2
3                   2          Shirt          5
4                   2          Pant           1
5                   3          Shirt          2

Rows 2 and 3 show that Person 2 has two shirts. So when you query this table for all accesories from Person 2, you would get 2 shirts and 1 pant.

As I said, you will need another table (or xml) where you can compare what ShirtType == 1 stands for and so on.