Can this table design be imrpoved upon?

[Guest]

Hi,

I have to design a database that lists the Organizations (e.g. YMCA), and
the Activities offered at each Organization (e.g. Swimming, Aerobics,
Volleyball) and the Age Group for which the Activity is offered (e.g.
Children, Adults, Seniors, or this could be the actual age-ranges).

I came up with the following tables:

1. tblOrganization (OrgID [PK], OrgName, OrgDescr..)
2. tblActivity (ActivityID [PK], ActivityName)
3. tblAgeGroup (AgeID [PK], AgeDescr)
4. tblOrgActivityAge (OrgID, ActivityID, AgeID) (the combination of all 3
fields is the PK)

Am I thinking in the right direction? What advantages can I list for my boss
for this design as compared to one big table?

Thanks.

-amit

 

[Tim Ferguson]

4. tblOrgActivityAge (OrgID, ActivityID, AgeID) (the combination of
all 3 fields is the PK)

Yup: this is in fifth normal form.

What advantages can I list for
my boss for this design as compared to one big table?

Correctness? There are no other designs that avoid deletion and insert
errors.

Age Group for which the Activity is
offered (e.g. Children, Adults, Seniors, or this could be the actual
age-ranges).

This could be a problem. You need to think very hard about having a single
coding system: or else Eric, who is a Senior, would be barred from doing
ElderlyQuoits because it's for 60 to 90 year-olds; or Fred might not do
MidRangeYoga becuase his Age is 45 and it's for MiddleAdults only.
IYSWIM...


All the best

Tim F

 

[Guest]

This could be a problem. You need to think very hard about having a single
coding system: or else Eric, who is a Senior, would be barred from doing
ElderlyQuoits because it's for 60 to 90 year-olds; or Fred might not do
MidRangeYoga becuase his Age is 45 and it's for MiddleAdults only.
IYSWIM...

Hi Tim,

Thanks for your response.

I'm not sure I understand how the above can be a problem. The AgeGroup
format has not been finalized yet, and I listed the two options that are
available. It could either be (e.g. [1-18], [19-64], [65+]) or (Children,
Adult, Senior). Only ONE of these will be finalized to be assigned to the
Activity at an Organization.

If someone wants to do a search by AgeGroup, the corresponding search
criteria will be available to them - either the actual age ranges, or the
Children/Adult/ Senior. These age ranges reflect the activity offered at
that particular organization, and not whether a person is actually capable of
doing that activity or not. Does this make sense?

-amit

 

[Immanuel Sibero]

Hi All,

Alternatively, you could use the following design:

- tblOrganization relates (Many-to-Many) to tblActivity with tblOrgActivity
as a junction table (note: this junction table is not currently in your
proposed design).

- tblOrgActivity relates (Many-to-Many) to tblAgeGroup with
tblOrgActivityAge as a junction table.


With the above design, there are a few advantages I can think of
immediately:
- You can create an activity for a particular organization without knowing
ahead of time which particular age groups the activity will be offered for.
With your proposed design, you can't.
- You can keep supplementary information related to a particular activity
offered by an organization. For example, the coordinator, days of the week
offered, etc. for Swimming at YMCA can be kept in tblOrgActivity. With the
proposed design, you can't.

Of course the overriding principle here is to model the table relationships
after the real world. I think joining all your three tables with one
junction table as you proposed put restrictions as demonstrated above. But
if this restrictive design can still effectively model your real world, then
that's all that's needed.




HTH,
Immanuel Sibero

 

[Guest]

Thanks, Immanuel. That was an excellent point!

-Amit

Hi All,

Alternatively, you could use the following design:

- tblOrganization relates (Many-to-Many) to tblActivity with tblOrgActivity
as a junction table (note: this junction table is not currently in your
proposed design).

- tblOrgActivity relates (Many-to-Many) to tblAgeGroup with
tblOrgActivityAge as a junction table.


With the above design, there are a few advantages I can think of
immediately:
- You can create an activity for a particular organization without knowing
ahead of time which particular age groups the activity will be offered for.
With your proposed design, you can't.
- You can keep supplementary information related to a particular activity
offered by an organization. For example, the coordinator, days of the week
offered, etc. for Swimming at YMCA can be kept in tblOrgActivity. With the
proposed design, you can't.

Of course the overriding principle here is to model the table relationships
after the real world. I think joining all your three tables with one
junction table as you proposed put restrictions as demonstrated above. But
if this restrictive design can still effectively model your real world, then
that's all that's needed.




HTH,
Immanuel Sibero

Hi,

I have to design a database that lists the Organizations (e.g. YMCA), and
the Activities offered at each Organization (e.g. Swimming, Aerobics,
Volleyball) and the Age Group for which the Activity is offered (e.g.
Children, Adults, Seniors, or this could be the actual age-ranges).

I came up with the following tables:

1. tblOrganization (OrgID [PK], OrgName, OrgDescr..)
2. tblActivity (ActivityID [PK], ActivityName)
3. tblAgeGroup (AgeID [PK], AgeDescr)
4. tblOrgActivityAge (OrgID, ActivityID, AgeID) (the combination of all 3
fields is the PK)

Am I thinking in the right direction? What advantages can I list for my boss
for this design as compared to one big table?

Thanks.

-amit

 

[Tim Ferguson]

It could either be (e.g. [1-18], [19-64], [65+]) or (Children,
Adult, Senior). Only ONE of these will be finalized to be assigned to
the Activity at an Organization.

Aha -- yes, that's the sort of thing I was getting at.

All the best

Tim F

 

[Tim Ferguson]

- tblOrganization relates (Many-to-Many) to tblActivity with
tblOrgActivity as a junction table (note: this junction table is not
currently in your proposed design).

- tblOrgActivity relates (Many-to-Many) to tblAgeGroup with
tblOrgActivityAge as a junction table.

The effective difference between this is that an activity can be offered
(i.e. there is a record in OrgActivity) for an undisclosed age group, which
may or may not be what you want. The 5NF version means that you can only
offer an Activity when the Organisation and the Activity are known. For
what it's worth, equally likely (and symmetrical) designs would be

Activities -< AreFor >- AgeGroups

AreFor -< ArrangedBy >- Organisations

(i.e. Activities are for people of certain Ages; and those opportunities
are arranged by the Organisations)

or even

Organisations -< CaterFor >- AgeGroups
Activities -< FitInWith >- CaterFor

(i.e. Organisations cater for people of certain Ages; and some Activities
fit in with those aptitudes). The last one is the least likely, probably,
but I don't know the OP's business nearly as well.

Of course the overriding principle here is to model the table
relationships after the real world.

Check


All the best


Tim F