x

Multiple attributes or Join tables?

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

 Example Case1:
 
 TBLACTIVI                
 ID_T    NAME    PROPERTY_1    PROPERTY_2    PROPERTY_3
 1    A    T    T    F
 2    B    T    F    T
 3    C    F    F    T
 4    D    F    T    F
 
 
 Example Case2:
 TBLACTIVI    
 ID_T    NAME
 1    A
 2    B
 3    C
 4    D
 
 TBLACTIVI_PROPERTIES    
 ID_AP    NAME
 1    PROPERTY_1
 2    PROPERTY_2
 3    PROPERTY_3
 
 NEW_TABLE    
 ID_T    ID_AP
 1    1
 1    2
 2    1
 2    3
 3    3
 4    2
more ▼

asked Jan 06, 2011 at 09:59 AM in Default

avatar image

RAVilledaRuz
23 1 1 4

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Jan 06, 2011 at 10:48 AM

avatar image

Grant Fritchey ♦♦
137k 20 42 81

(comments are locked)
10|1200 characters needed characters left

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.
Louis Davidson's blog

more ▼

answered Jan 06, 2011 at 10:03 AM

avatar image

Tim
40.4k 39 83 166

  • for Louis Davidson

Jan 06, 2011 at 10:48 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

Split definitely!

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!

more ▼

answered Jan 06, 2011 at 11:18 AM

avatar image

Kev Riley ♦♦
63.8k 48 61 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x76
x16

asked: Jan 06, 2011 at 09:59 AM

Seen: 2159 times

Last Updated: Jan 06, 2011 at 09:59 AM

Copyright 2016 Redgate Software. Privacy Policy