Re: How to represent different types of people

If the person is a manager, just store a null in the Goal entity. It's a hack, but it works.

Although I'm sure there's a more correct and elegant way to do this, you will be able to join the tables and exclude the nulls, when necessary.

-ERMREY "Kev" <kevin.porter_at_fastsearch.com> wrote in message news:3A30AFE7.EC4132EA_at_fastsearch.com...
> OK thanks, but this much I know.
>
> I have a Person and a Job table, so I can represent managers and players
 no
> problem. The difficullty comes
> with the fact that there should be a relationship between a Player
 (represented
> by the Person_Job
> entity) and a Goal (the relationship being 'Scored'). However, I can't
 just
> simply draw a line from
> Person_Job to goal on my ERM, because a Manager (which is also represented
 by
> the Person_Job table) does not
> score goals. But there are other entities that a Manager needs to be
 related to
> which a Player doesn't.
>
> How do I resolve this conflict on an ERM, and what is the usual way of
> implementing it?
>
> thanks,
>
> - Kev
>
>
> Alan wrote:
>
> > I'm not sure I fully understand the prblem, but it sounds like you have
 the
> > following
> >
> > Entities: People, Jobs
> >
> > A person can have more than one job and a job can be performed by more
 than
> > one person; thus you have a many-to-many. You are concrened that a job
 may
> > appear in the Jobs table that is not approp[riate for a person in the
 People
> > table. This is not a problem at all. Anyway, the way you can solve this
 is
> > by creating three tables (this is the standard many-to-many solution);
> >
> > People PK= person_id
> > Jobs: PK= job_id
> > People-Jobs PK=person_id, job_id
> >
> > Join People to People-Jobs on person_id
> > Join Jobs to People-Jobs on job_id
> >
> > You need to create an entry in People-Jobs for every combination of
 people
> > and jobs that you have., so
> >
> > person
> > 1
> > 2
> > 3
> >
> > job
> > a
> > b
> > c
> >
> > people-job
> > 2 a
> > 2 b
> > 1 a
> > 1 c
> >
> > Let's say person #1 is a manager. the manager job id is c. You would
 have an
> > entry in people-job:
> > 1 c
> >
> > Very simple.
> >
> > "Kev" <kevin.porter_at_fastsearch.com> wrote in message
> > news:3A2F9474.CA1B1DB6_at_fastsearch.com...
> > > I can assure you that it is not an exam question - I'm a programmer on
 a
> > > football site (http://www.fa-premier.com)
> > > and this is defeinitely a real-world problem!
> > >
> > > My solution so far is to have a Person class, which is subtyped to
 Player
 and
> > > Manager. I can represent this
> > > on an ERM diagram easily enough using an arc (or a circle with a 'd'
 in).
 In
> > > implementation I guess I would
> > > have to have a 'type' field in the Person table, and then add a 'where
> > > person.type = player' clause (or
> > > something to that effect). Is this an acceptable way of implementing
 it,
 or is
> > > there some other 'standard' way
> > > I should be aware of?
> > >
> > > thanks,
> > >
> > > - Kev
> > >
> > > Alan wrote:
> > >
> > > > Sounds a lot like a question on a take-home final. So, to not give
 you
 the
> > > > final answer, think about the cardinality of the relationships, and
 don't
> > > > get too hung up on what you call things. They are only nouns and
 verbs.
> > > > "Plays_for" could be named anything, like "performs_job_of", or
> > > > "whose_job_title_is". But that's not the big problem with what you
 are
> > > > doing...
> > > >
> > > > "Kev" <kevin.porter_at_fastsearch.com> wrote in message
> > > > news:3A2E3E70.9480788_at_fastsearch.com...
> > > > > Hi,
> > > > >
> > > > > I have a design question I hope someone can help me with...
> > > > >
> > > > > I'm designing a database relating to sportt, a part of which needs
 to
> > > > > represent different
> > > > > types of people, 'different types of people'
> > > > > meaning 'people with different jobs'. So, I have made a 'Job'
 entity
 on
> > > > > my ERM and a 'Person' entity. I have
> > > > > a third entity called 'Person_Job' which represents an instance of
 a
> > > > > person's employment. Person_Job stores
> > > > > a start and end date too.
> > > > >
> > > > > The problem I'm having is that different types of people need to
 have
> > > > > different relationships with other
> > > > > entities in the system. For example, I cannot simply add a
 relationship
> > > > > labelled 'Plays for' between
> > > > > Person_Job and the Team entity, because although that would be
 correct
> > > > > for a player it would not be
> > > > > correct for a manager, and there are other relationships which are
 true
> > > > > for a manager but not for a player.
> > > > >
> > > > > What is the correct way of dealing with this situation on an ERM?
 And,
> > > > > what is the correct way of
> > > > > implementing it?
> > > > >
> > > > > thanks,
> > > > >
> > > > > - Kev
> > > > >
> > >
>
Received on Thu Dec 21 2000 - 06:10:28 CET