Stuck up with table design

[byfr7]

Hi,
I am working on a database for tracking activities done by location. My problem is how to split my Act table since I found the Act definition is really complicated. For example, there are ten activities:
(ACT1,ACT2,ACT3, ACT4, ACT5, ACT6, ACT7, ACT8, ACT9, ACT10) and some activities are related to some specific subactivities:
ACT SubAct
1 no
2 no
3 3sub1
3 3sub2
4 no
5 5sub1
5 5sub2
5 5sub3
5 5sub4
5 5sub5
etc
and the worse part is every Act have some different fileds to fill and so do the combination of Act and SubAct.

Could anyone tell me how to handle this???

Regards,
May

 

[TheAceMan1]

How are ya byfr7 . . . . .

Right off the top of my head, you have two tables. [blue]Act[/blue] and [blue]subAct[/blue]. With a relationship having [blue]Act[/blue] on the one side, [blue]subAct[/blue] on the many. This way you can have as many subActs for an Act as you like . . . .

See Ya! . . . . . .

 

[byfr7]

Thanks for your reply. I think I didn't describe my question right. The activities that have subactivities will are bound together and the subactivities cannot be changed. According to my example above, ACT3 can only have two subactivities 3sub1 and 3sub2.

 

[lespaul]

It's the same as a Bill of Materials setup:

A BOM has items - some of the items are made up of other items, but they are all still items.

You have Acts - it doesn't matter if they are primary acts or sub acts, they are all still acts:

tblActslist all acts and sub-acts in this table
ActID
ActDescription

then you have the combonation of sub-acts to make up the primary acts:

tblActAssembly
PrimaryActID
SubActID

the combination of the two IDs will be a composite PK.

You may want to check out the paper 'Fundamentals of Relational Database Design' which you can find at TTer JeremyNYC's website in the developer's section:

http://www.alphabetcitydataworks.com

HTH

leslie

 

[byfr7]

Thanks a lot leslie, I am going to try what you suggest.

 

[r937]

leslie, you keep pointing to that Word doc when there's a web page version of the exact same article here:

http://r937.com/relational.html

of course, if you like Word docs, that's fine, but me, i hate Word docs a lot (which is one of the reasons i approached the author to ask if i could mirror the article in HTML)

rudy

http://r937.com/

SQL Consulting