Table Design Help With Widely Variable Data

[Auguy]

Need some help with table design in SQL 2005. I have an employee table that will have the usual name, address, DOB, etc. I also have 18-20 “categories” that I need to associate with each employee record with each category having anything from a few text or check boxes to a small checklist of items (5-10) with a column for a description and a column to enter a date or text. I've done master detail tables before with things like an Invoice Header and Invoice Detail tables, but the kind of data I need to store in this project are so widely different from category to category that I don't know if that will work. For example, I have some categories that have 5-10 items that need a date associated with them, another category needs several combo box selections, and another has both types of controls along with text boxes. I could try and put all of category information in one table but that would lead to some fields only being used by one or a few categories. I think the alternative is to have a table for each category and that doesn't seem like a good approach either. Maybe there is enough commonality in some of these categories that I would only need a few detail tables. I’m also trying to code the application to provide for the possibility of adding selections to some of the lists and not having to change the code.

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

You may want to consider using an Entity-Attribute-Value (EAV) structure to hold this data. Generally speaking, I am not a big fan of EAV because it can quickly become a pain to query the data, but there are some advantages to it also.

Basically, your table structure could look like this:

EmployeeId Int
CategoryId Int
Name varchar(20)
Value varchar(something big)

Then your data might look like this:

[tt]
EmployeeId CategoryId Name Value
1 1 Eye Color Brown
1 1 Shoe Size 9.5
2 1 Eye Color Blue
2 5 Address1 101 Main St.
2 5 City Any Town
[/tt]

There are other ways you can structure this data. For example, you could is an ID instead of name. You could even have another table for the name, like this:

[tt]
CategoryId AttributeId Description
1 1 Eye Color
1 2 Shoe Size
2 1 Favorite Color
2 2 Favorite Food
[/tt]

In this table, you could store the data type for the attribute, like Date, Integer, Decimal, etc...

Before you get too far down this path, I strongly encourage you to do some research about the pro's and con's of this data model.




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

Thanks George

Auguy
Sylvania/Toledo Ohio