Table Design Question...

Question

Table Design Question...

Peter-218449

Mr or Mrs. 500

Points: 557
More actions
December 10, 2008 at 8:51 am

#216389

Hi,
I have a design question about the best place to put columns that apply to more than one entity. I will describe a very simplified version of the issue:
Let's say I want to store different three different types of food: Fruit, Vegetables and Nuts. I will create Food Type, Food, Fruit, Vegetable and Nut as my tables.
Some attributes are common to all food, such as the region in which is produced. These are stored in the food table. Others are specific to the particular food type. For example, fruit has a number of seeds attribute and nut has a shell strength attribute. So number of seeds would be stored in the fruit table and shell strength in the nut table.
Let's say the attribute color is applicable to fruit and vegetable but not nut. I have three options on how to implement this. All three are illustrated in the attached pictures.
Case 1:
Place a color column in both the fruit and the vegetable table. This will ensure that color is only applied to the correct food types but if I want to find food of a certain color I need to look in both tables.
Case 2:
Place a color column in the food table. It will be easy to search for the color of a food type but I will need to ensure that color is not added for any food of type nut. I will also have nulls for color for all food of type nut. Personally, I don't think this is a good option but since it is a choice I have included it.
Case 3:
Create another table that has a food ID and a color column. In this scenario color is in one place to ease searching but I will have to police that a color is not entered for a food of type nut.
Any thoughts are welcome. Thank you in advance.
Pete

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 1

Without knowing more about your application, data, etc its almost impossible to determine which design is best. I would tend to put an attribute like color on the Food table and have it nullable or populate the default single color for ones that it does not apply.

However, another option is to create something like a food attribute table. A lot of people use the EAV style design pattern for information like this. So you could store the attribute and its value in a table for each food.

For example:

FoodId, AttributeId, Value

Peter-218449 Mr or Mrs. 500 Points: 557 More actions · Answer 2

Hi,

Thank you for your response. I tried to use the food example as a simplified means of explaining my issue, perhaps I simplified it too much.

My app will have around several types and several layers of subtypes. So at the end of the day, the number of possible combinations will be in the hundreds. Among those hundreds of possibilities there are several overlapping attributes. Within a sub type of a possible twenty, 15 may share 3 attributes, another 12 may share 5 attributes and so on.

EAV will not work due to my search requirements. It will be too slow.

To keep with my example as the analogy. Someone could create a granny smith apple which would have all attributes of the food, fruit, apple and granny smith tables. Or a carrot where the attributes would come from food, vegetable, carrot tables. It may be that both the granny smith apple and the carrot share a few attributes but several others in the hierarchy do not. Maybe something like 'tasty when eaten raw' which does not apply to several vegetables (potato) or fruits (lemon) Does that help to clarify?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

EAV won't be a slow as you think. It'll process about 12 million rows in about 7 seconds.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

Depending on how you implement search, it might work well. I wouldn't be afraid of EV, I'd think about or look for good search code.

Maybe you should mock up a few tables, run some searches with sprocs and see how things perform. Then ask about specific issues with the code.

Peter-218449 Mr or Mrs. 500 Points: 557 More actions · Answer 5

Thanks for the responses. I looked at EAV a few months ago and it was an absolute mess. It performs searches for an entire entity decently but having searches with several conditions that needed to join together many large tables turned out to be a nightmare. That is not a road I want to travel down again.

With that said, any thoughts on the three designs I attached to the first post?