Alex,
Yeah, we didn't have time to cover subtypes in the Database Design class.
They are an advanced topic.
A subtype cluster refers to a group of entities sharing similar
characteristics that form a "generalization hierarchy." Each subtype cluster
has a parent entity, known as a supertype, that encompasses each of the
subtypes. The subtypes represent homogeneous subsets of the supertype. A
particular attribute, referred to as a discriminator, can distinguish the
entities from each other.
Take the example of the group of entities - Employee, StoreManager,
SalesPerson and StockPerson. Each entity stores information about employees.
Accordingly, Employee is the supertype in this example and the rest are
subtypes. Any employee contained in a subtype is also represented in
Employee. In each entity, the primary key is the EmployeeID. The
discriminator for this subtype cluster may be an attribute, such as
EmployeeType. The StoreManager entity would only contain store managers, the
SalesPerson entity represents only sales people, and so on.
Other than recognizing a generalization hierarchy, perhaps the critical
issue with subtyping is deciding how to implement a subtype cluster in the
physical design. Sometimes it is best to project subtypes into the
supertype, representing the entire cluster as a single, generalized entity.
Other times, you may need the granularity of separate subtype entities,
choosing to implement the subtypes and supertype one-for-one or rolling the
supertype down into the subtypes. I have modeled systems both ways. Give
me a call tomorrow at the number below and I'll help you through how to
decide.
Respectfully,
Adam Phillip Churvis
Productivity Enhancement, Inc.
Want truly advanced ColdFusion 5 training? Register for the Advanced
Bootcamp for ColdFusion 5, Database, and Javascript at
http://www.ColdFusionTraining.com!
http://www.CommerceBlocks.com
E-mail: [EMAIL PROTECTED]
Phone: 770-446-8866
----- Original Message -----
From: "Alex Aguilar" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, May 17, 2001 4:32 PM
Subject: Re: database modeling
> thanks for the responses everyone.
> I'm still not sure what to do. I'm going to finish fleshing out the rest
of
> the model before deciding.
>
> Adam,
> What do you mean by "subtype"? Isn't that more of an OO concept.
> How do you model a subtype in a relational db? This project is my first
stab
> at developing a database from scratch. So any advice or good design
> technique articles are very much apprecitated.
>
> thanks,
> alex
> ----- Original Message -----
> From: "Adam Phillip Churvis" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Thursday, May 17, 2001 3:28 PM
> Subject: Re: database modeling
>
>
> > Start off by modeling entities, not tables (and remember that they _are_
> > different).
> >
> > If they are different entities, then model them that way. If they are
two
> > subtypes of the same entity, then model them as the same entity with two
> > subtypes. When the logical model is resolved into a physical model,
then
> > you might end up merging the attributes of these two subtypes into a
> single
> > table with the mutually-exclusive subtype columns NULLable.
> >
> > Also take into consideration any relationships your model might have
> between
> > the Player entity and a "Team" entity: a Prospect most likely is not
> related
> > to a Team, and that is something much more significant that a "Status"
> value
> > separating Players from Prospects. Don't try to accommodate this by
> putting
> > both Prospects and Players into a single table and making the
relationship
> > between Player and Team optional rather than mandatory. If you do, then
> > you're letting the tail wag the dog.
> >
> > Respectfully,
> >
> > Adam Phillip Churvis
> > Productivity Enhancement, Inc.
> >
> > Want truly advanced ColdFusion 5 training? Register for the Advanced
> > Bootcamp for ColdFusion 5, Database, and Javascript at
> > http://www.ColdFusionTraining.com!
> >
> > http://www.CommerceBlocks.com
> >
> > E-mail: [EMAIL PROTECTED]
> > Phone: 770-446-8866
> > ----- Original Message -----
> > From: "Alexander Aguilar" <[EMAIL PROTECTED]>
> > To: "CF-Talk" <[EMAIL PROTECTED]>
> > Sent: Thursday, May 17, 2001 12:41 PM
> > Subject: OT: database modeling
> >
> >
> > > I'm working on an amateur sport team's website and they of course want
> to
> > keep track of their player information. On their website they want a
> > contact/membership form for prospective players. Now the data that is
> > stored for a Player and a Prospect is very similar (personal info
[height,
> > weight, age], contact info and prowess [batting style, specialty,
> > experience,etc]. A prospect is not a member of the team until approved
by
> > the team.
> > >
> > > Initially, I was leaning toward building a Prospects table but as I
> > mentioned the fields are basically the same as the Player table. Now
I'm
> > thinking I should put prospect data directly into the Player table but
> > differentiate it by using a "status" column which would one of the three
> > following values (active | inactive | prospect). My question is which
is
> > the better way to handle Prospects (ie, separate Prospect table or use
> > Player table with status field) ?
> > >
> > > thanks,
> > > alex
> > >
> > >
> > >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
