Design help

[C. Walters]

Hi folks,

I have to design a table or tables to contain the
following type of info:

OrgNbr
OrgName
SubOrg1
SubOrg2
SubOrg3
SubOrg4
SubOrg5

Each Org can have multiple SubOrgs so I would end up with
the same Info being repeated in some of the fields.
I though that if I broke the table down into:

OrgNbr OrgNbr SubOrg1 SubOrg2
OrgName SubOrg1 SubOrg2 SubOrg3...

I would at least alleviate some of the redundancy. Now my
question - for anyone who has managed to follow thus far
is this. I have to associate a client with an Org, but do
I really have to store all of the Org and SubOrg numbers
with the client or is their another way? Right now we
have each client associated with an Org, but we want to
break down the Org into these separate pieces and then
associate the client with that. Can I get away with
storing just one number or do I have to associate all
SubOrgs and the Org with the Client?

Any ideas are appreciated.

C

 

[Lynn Trapp]

You should break that out into 2 tables.

tblOrganizations
OrgNbr (Primary Key)
OrgName
....Other fields releated to an Organization

tblSubOrganizations
SubOrgNbr (Primary Key)
OrgNbr
....Other Fields related to a Sub Organization

Now you can create as few or as many SubOrgs as you need for each
Organization.

 

[C. Walters]

Yes, I am doing that. Now, how do I relate that back to
a client? Suppose I have 2 clients, 1 is associated with
Org 1, the other is associated with Org1, SubOrg1. Which
Org/SubOrg number do I store with the client? All?

 

[Lynn Trapp]

That depends entirely on your design and your business needs. Ask yourself
some questions and it will probably lead you to the answer.

1. Can a given Organization have more than one Client?
2. Can a given Sub Organization have more than one Client?

If an Organization can have more than one Client AND a Sub Organization can
have more than one client, then you will probably want to have your third
table related to the Sub Organization table. If an Organization can have
only one Client and, therefore, a Sub Organization can have only one client,
then you can relate it to the Organization table.