Correct table structure for a Division w/ Subdivisions?

[andmunn]

Hello,

I'm starting to develope a new database, and want to make sure i've got my table structure where it should be. I have 3 tables:

tblDivision
> pkDivID
> txtDivision

tblSubDivision
> pkSubDivID
> fkDivID
> txtSubDivision

tblPerson
> pkPersonID
> txtFirstName
> txtLastName

My problem arises from the fact that i want to link a person (from tblPerson) to each subdivision. Would i do that by ading a foreign key in tblSubDivision that links to tblPerson? Is that the right way to go about doing this? Is the way tblSubDivision is laid out (with foreign key to tblDivison) accurate?

Thanks!

Andrew.

 

[MSAccessRookie]

Hello,

I'm starting to develope a new database, and want to make sure i've got my table structure where it should be. I have 3 tables:

tblDivision
> pkDivID
> txtDivision

tblSubDivision
> pkSubDivID
> fkDivID
> txtSubDivision

tblPerson
> pkPersonID
> txtFirstName
> txtLastName

My problem arises from the fact that i want to link a person (from tblPerson) to each subdivision. Would i do that by ading a foreign key in tblSubDivision that links to tblPerson? Is that the right way to go about doing this? Is the way tblSubDivision is laid out (with foreign key to tblDivison) accurate?

Thanks!

Andrew.

Some questions to ask yourself

• Can more than one person be associated with a single Sub-Division?
• Can an individual person be associated with more than one Sub-Division?

If the answer to both of these questions is NO, then adding a Foreign Key in tblSubDivision that links to tblPerson would work, but is not recommended because the design does not allow for any change in the relationships.

If the answer to either of these questions is YES, then you would want to create a Junction Table to define the relationships between tblSubDivision and tblPerson.

 

[georgedwilkinson]

I'm not sure what a division or sub-division is, actually. Is it a geographic boundary? A sports team classification?

At any rate, if it is a geographic boundary, wouldn't a person be in one or more sub-divisions? When I consider the motion of people, I see them move 10 or more miles in a day, meaning they "go through" multiple geographic areas. If that is the case, your relationship would be a M:M relationship (indicating you need to create a junction or associative table).

Same thing on sports classifications. Can a competitor be in only one division, or in many? What happens next year...same division or a different one? What about competitors that compete in multiple divisions or multiple sports, or go to multiple schools during the database active period? Again, you'd probably want a M:M relationship to fully define all the possibilities.

My general rule is that if you think you need to put a column in a table that just doesn't belong to the base type within that table, the relationship might be a candidate for a M:M relationship.

For instance, in the real world people are not defined by where they live, drive, or what sports they indulge in. They are defined as people by a very small number of data fields. So if I ever think I need to put something in the people table that defines them as a sports enthusiast or a resident, I rethink my design. Anyways, such classifications are often fleeting.

 

[andmunn]

Hello,

Thanks for the help. Perhaps i'll clarify a little. This database is for a large international company, which have products in several key areas (divisions). For example, an international company selling sporting goods:

Divisisons:
1. Hockey
2. Baseball
3. Tennis

Subdivisions
Hockey
> Equipment
> Memorablia
> Other
(etc for the other 3 divisions).

Therefore, each "Division" will have a "Division Lead", and each subdivision will also have a "manager". The same person can be responsible for several different divisions / subdivisions.

I believe, what was said above, is i need to create a junction table, does this look correcT?

tblJunctionPersonDivisionSubDivision (junction between person / tbldivision / tblSubvision).
> pk
> fkPerson
> fkDivision
> fkSubdivision
> IsDivisionLeader (if this person manages the "whole division - i.e.// hockey").

Am i on the right track?

Andrew.

 

[jdraw]

Hello,

Thanks for the help. Perhaps i'll clarify a little. This database is for a large international company, which have products in several key areas (divisions). For example, an international company selling sporting goods:

Divisisons:
1. Hockey
2. Baseball
3. Tennis

Subdivisions
Hockey
> Equipment
> Memorablia
> Other
(etc for the other 3 divisions).

Therefore, each "Division" will have a "Division Lead", and each subdivision will also have a "manager". The same person can be responsible for several different divisions / subdivisions.

I believe, what was said above, is i need to create a junction table, does this look correcT?

tblJunctionPersonDivisionSubDivision (junction between person / tbldivision / tblSubvision).
> pk
> fkPerson
> fkDivision
> fkSubdivision
> IsDivisionLeader (if this person manages the "whole division - i.e.// hockey").

Am i on the right track?

Andrew.

Further to what George and the Rookie said, I would suggest you start making a list of rules/business rules. Also, you could make a list of the types of questions or reports you may want you database to support.
You can mock up some tables and structure and go through each of your rules and each of your questions/reports to ensure your design matches those needs.
If it doesn't satisfy a requirement, then adjust accordingly and test again. Nobody creates the perfect, long term design first time. Don't be afraid of some testing and reconfiguring.

You can find info on business rules and data models here:

http://www.databaseanswers.org/data_models/avon_cosmetics/facts.htm

This is for example of the types of questions, level of detail only. Your situation may be different, but the examples at this site should be helpful.

 

[georgedwilkinson]

I believe, what was said above, is i need to create a junction table, does this look correcT?

tblJunctionPersonDivisionSubDivision (junction between person / tbldivision / tblSubvision).
> pk
> fkPerson
> fkDivision
> fkSubdivision
> IsDivisionLeader (if this person manages the "whole division - i.e.// hockey").

Am i on the right track?

Andrew.

As far as being on the right track, you're getting closer.

As far as the table design being correct, "no". There is no reason to include the Division foreign key in the junction table as you already have that from the Sub-division.

As far as whether it is overall what seems right:
<personal opinion><soap box>
Table names should represent real life business objects. Divisions and Sub-divisions are not real life business objects...they are too fleeting. In fact, they might be accurate business names for a Real Estate system.

Aren't Divisions and Sub-divisions really business entities? And aren't the "People" in your model really just responsible for some activity within those business entities? And can't their involvement change in almost any way, including the level of their involvement, the business entities which they're involved with, how they are involved, etc., over time?

Many people in the US would have called your Divisions "Departments". And a department is a business entity also known as an Organization. Any Organization can have zero or more relationships with any other Organization. Same with "People". And roles.

I'm just saying that what you're doing has already been done and that data modelers in the know have a template ready to go for this. In addition to the link that jdraw pointed to, you could take a look at tdan.com, specifically at http://www.tdan.com/view-articles/5014. Though on the surface, the models described are more complex than you are describing, those models will continue to work when you get a new VP of Sales who wants to do everything "his" way. It will also work for any industry. There is no way that I know of to describe a way of doing business that it will not work for.
</soap box></personal opinion>

HTH.

 

[andmunn]

Thanks everyone for your help. Extremely appreciated.

Goerge - thanks for that link - this is somethign i believe i can continuosly exploit. Thanks so much.

Andrew.