Re: How to represent different types of people

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 07 2000 - 22:05:12 CET