Excessive Normalization? One table or multiple tables?
Posted by: Joe Koston
Date: February 14, 2011 11:53PM

This is a relatively manufactured example but bear with me as it will illustrate the point.

Lets say I have a table called Balls, and this stores all the information about the different Balls I have. They can be soccer balls, basket balls, etc.

Lets say I also have a table called Planets and this stores information about planets.

So, each Ball and Planet is a sphere and has some required info. I.e. the radius, diameter, or volume.

Both Ball and Planet share this 'property_type' field and a 'property_value' field. Depending on which property_type selected the value field will be interpreted differently.

So, the question I have is..should I extract the property to another table or add a property_type and property_value field to each table

So far I've come up with these pros/cons, but I don't know enough real world data/experience to decide if one approach is better than another.

Creating a separate table:
Pros:
- If I add more types later it's simpler since it requires updating only one table ENUM value which is in the shared property table.
- If I'm not looking at the type very often, theres no 'need' to have the type and value in the main object table. The table is bigger than needed since I can move these values out to another table. (Does that even make a big difference on anything by having more columns in the table?)

Cons:
- I have to join with another table to find the property.
- Seems kind of weird if an object that has only one property, and it isn't shared with another object, to put that property in in another table?

Keeping in a single table:
Pros:
- Query is simple because nothing special needed to get property value and type

Cons:
- If I add more types later then I have to update every table..

So any input? So far I think the crucial part here is deciding how often the property_type will change, and how often I'll need to even look at the property data.

In my case the property_value is used to calculate some data points. Most queries will work with the calculated data points, but I need to keep the property value around to know how I got the calculated data, and be able to recalculate later. (i.e. the user provided a radius, and I used it to calculate the surface area. When the user edits the info I want to show the radius they input originally).

Another question on this is, how do you best handle values like this that are interpreted differently based on another field? In my case they are all numbers so it's simple. But what if I wanted a field that could be polymorphic and be be a number or a string?

Hopefully this makes sense.

Thanks in advance for your replies

Options: ReplyQuote


Subject
Written By
Posted
Excessive Normalization? One table or multiple tables?
February 14, 2011 11:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.