I am fairly new here. Let me know if I am posting at wrong place. I have a question regarding database design.
We have a table (TBLACTIVI) containing activities and their properties (attributes). An activity can meet various properties. However, as more properties are added, the table grows horizontally (adding more attributes) and this is filled with values T or F as appropriate.
One option we have is to leave these features in another table -TBLACTIVI_PROPERTIES this table would have the catalog of properties- and a third table that links the two tables. Thus, in this way, when an activity has a property, we fill the record in the intermediate table.
Which of the designs is best?
With an option, we will end with a table containing multiple attributes and the access would be via index.
With the other option, we remove such access but to find the properties will require an join operation between 3 tables.
Thanks in Advance, R.A.VilledaRuz
asked Jan 06, 2011 at 09:59 AM in Default
I would absolutely split out the table and use joins. As a rough idea of what I'd do (again, rough, so don't take this as absolute truth), I'd create three tables. One, the original ACTIVITY table. The second, call it PropertyType, would contain definitions for properties, as much information as you needed on them, maybe just a name and id, but possibly descriptions, what not. Then, a third table, call it ActivityProperty. In this one you create a foreign key constraint to each of the other tables, and make that the primary key (that prevents duplicates from the PropertyType table), and a value column. Then, you can have any number of properties and adding them is simply a matter of adding another row to the PropertyType table.
answered Jan 06, 2011 at 10:48 AM
Grant Fritchey ♦♦
You really want to normalize your database. Even though when starting out JOIN's can feel like a pain, they are quite simple. Creating very wide columns that are not relevant to everything in the table can waste a great deal of space. I would highly recommend reading a few blogs about database design. You are on the right track coming here and asking questions and for being forward thinking about how your design will operate long term. I vote to split the data out and use a few joins. When I think about database design Louis Davidson pops up in my head.
Put data in rows not columns, then you can use indexes!
How many properties can each scenario handle - the number of columns a table can have is limited (for SQL 2008 R2: 1024 for a non-wide table, 30000 for a wide one) - the number of rows is only limited by what disk space you've got - theoretical I know, but it illustrates the point!
answered Jan 06, 2011 at 11:18 AM
Kev Riley ♦♦