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

RAVilledaRuz gravatar image

RAVilledaRuz
23 1 1 2

(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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98k 19 21 74

(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

Tim gravatar image

Tim
36.4k 35 41 139

+1 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

Kev Riley gravatar image

Kev Riley ♦♦
52.7k 47 49 76

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x107
x13

asked: Jan 06, 2011 at 09:59 AM

Seen: 1822 times

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