Database design question

[Jacob Hatley]

Hi

I'm new to relational database design and I was wondering if someone could
help me with the following problem:

I have to create a database that has to account for meetings that are held
and all the information pertaining to those meetings. So let's say we create
a table called 't_meetings'. In this table you would have fields like
member_id, etc... My question is if I design a "meeting" table, every
record in that table is going to represent 1 meeting -- this is what I do
*not* want because I must have duplicate meeting ID's since many members can
attend the same meeting - so having a meeting id as the primary key would
not work. What I want is a design that will allow me to get information
about different members of a certain meeting (like who attended, and what
they bought, etc.) -- I need a better way of thinking about this.

IOW, if you had to create a table that is used to track what happens at a
meeting, how would you go about it?

Does this make sense? Please, any ideas are helpful

thank you

[Ed Butler]

Jacob,

What you are describing is a classic "many-to-many" relationship. To model
this correctly you will need to use 3 tables rather than one. Like this:

Meeting {MeetingID, MeetingDate, Subject, Location, ...}
Person {PersonID, FirstName, LastName, Title, ...}
MeetingAttendance {MeetingID, PersonID}

This would be easier to understand w/ an Entity-Relationship diagram, but
that's king of hard to do in plain text format...

To answer the question: "Who attended the recent Finance meetings" you'd
use a query like this:

select p.FirstName, p.LastName, m.Subject, m.MeetingDate
from Person as p
inner join MeetingAttendance as ma
on ma.PersonID = p.PersonID
inner join Meeting as m
on m.MeetingID = ma.MeetingID
where m.Subject like '%finance%'
and m.MeetingDate > '07/01/2000'


Hope this points you in the right direction...

--Ed Butler


"Jacob Hatley" <JHatley@aol.com> wrote:
>Hi
>
>I'm new to relational database design and I was wondering if someone could
>help me with the following problem:
>
>I have to create a database that has to account for meetings that are held
>and all the information pertaining to those meetings. So let's say we create
>a table called 't_meetings'. In this table you would have fields like
>member_id, etc... My question is if I design a "meeting" table, every
>record in that table is going to represent 1 meeting -- this is what I do
>*not* want because I must have duplicate meeting ID's since many members
can
>attend the same meeting - so having a meeting id as the primary key would
>not work. What I want is a design that will allow me to get information
>about different members of a certain meeting (like who attended, and what
>they bought, etc.) -- I need a better way of thinking about this.
>
>IOW, if you had to create a table that is used to track what happens at
a
>meeting, how would you go about it?
>
>Does this make sense? Please, any ideas are helpful
>
>thank you
>
>

[Jacob Hatley]

Thanks, Ed. That does help. I may have a few more questions later, but that
certainly helps for now.

Thanks


"Ed Butler" <ebutler@aps-now.com> wrote in message
news:396cb64c$1@news.devx.com...
>
> Jacob,
>
> What you are describing is a classic "many-to-many" relationship. To
model
> this correctly you will need to use 3 tables rather than one. Like this:
>
> Meeting {MeetingID, MeetingDate, Subject, Location, ...}
> Person {PersonID, FirstName, LastName, Title, ...}
> MeetingAttendance {MeetingID, PersonID}
>
> This would be easier to understand w/ an Entity-Relationship diagram, but
> that's king of hard to do in plain text format...
>
> To answer the question: "Who attended the recent Finance meetings" you'd
> use a query like this:
>
> select p.FirstName, p.LastName, m.Subject, m.MeetingDate
> from Person as p
> inner join MeetingAttendance as ma
> on ma.PersonID = p.PersonID
> inner join Meeting as m
> on m.MeetingID = ma.MeetingID
> where m.Subject like '%finance%'
> and m.MeetingDate > '07/01/2000'
>
>
> Hope this points you in the right direction...
>
> --Ed Butler
>
>
> "Jacob Hatley" <JHatley@aol.com> wrote:
> >Hi
> >
> >I'm new to relational database design and I was wondering if someone
could
> >help me with the following problem:
> >
> >I have to create a database that has to account for meetings that are
held
> >and all the information pertaining to those meetings. So let's say we
create
> >a table called 't_meetings'. In this table you would have fields like
> >member_id, etc... My question is if I design a "meeting" table, every
> >record in that table is going to represent 1 meeting -- this is what I do
> >*not* want because I must have duplicate meeting ID's since many members
> can
> >attend the same meeting - so having a meeting id as the primary key would
> >not work. What I want is a design that will allow me to get information
> >about different members of a certain meeting (like who attended, and what
> >they bought, etc.) -- I need a better way of thinking about this.
> >
> >IOW, if you had to create a table that is used to track what happens at
> a
> >meeting, how would you go about it?
> >
> >Does this make sense? Please, any ideas are helpful
> >
> >thank you
> >
> >
>

[Jacob Hatley]

What if I each location has a specific location code -- to show where it
is... would I have a 'MeetingCenters' table with a CenterId, address, city
state, zip, description field and then relate that in the meeting table for
the location?


"Ed Butler" <ebutler@aps-now.com> wrote in message
news:396cb64c$1@news.devx.com...
>
> Jacob,
>
> What you are describing is a classic "many-to-many" relationship. To
model
> this correctly you will need to use 3 tables rather than one. Like this:
>
> Meeting {MeetingID, MeetingDate, Subject, Location, ...}
> Person {PersonID, FirstName, LastName, Title, ...}
> MeetingAttendance {MeetingID, PersonID}
>
> This would be easier to understand w/ an Entity-Relationship diagram, but
> that's king of hard to do in plain text format...
>
> To answer the question: "Who attended the recent Finance meetings" you'd
> use a query like this:
>
> select p.FirstName, p.LastName, m.Subject, m.MeetingDate
> from Person as p
> inner join MeetingAttendance as ma
> on ma.PersonID = p.PersonID
> inner join Meeting as m
> on m.MeetingID = ma.MeetingID
> where m.Subject like '%finance%'
> and m.MeetingDate > '07/01/2000'
>
>
> Hope this points you in the right direction...
>
> --Ed Butler
>
>
> "Jacob Hatley" <JHatley@aol.com> wrote:
> >Hi
> >
> >I'm new to relational database design and I was wondering if someone
could
> >help me with the following problem:
> >
> >I have to create a database that has to account for meetings that are
held
> >and all the information pertaining to those meetings. So let's say we
create
> >a table called 't_meetings'. In this table you would have fields like
> >member_id, etc... My question is if I design a "meeting" table, every
> >record in that table is going to represent 1 meeting -- this is what I do
> >*not* want because I must have duplicate meeting ID's since many members
> can
> >attend the same meeting - so having a meeting id as the primary key would
> >not work. What I want is a design that will allow me to get information
> >about different members of a certain meeting (like who attended, and what
> >they bought, etc.) -- I need a better way of thinking about this.
> >
> >IOW, if you had to create a table that is used to track what happens at
> a
> >meeting, how would you go about it?
> >
> >Does this make sense? Please, any ideas are helpful
> >
> >thank you
> >
> >
>

[Ed Butler]

Jacob,
You are correct. You would replace the "Location" field in my example with
a "LocationID" field that relates to your MeetingCenters table. The standard
database lingo for that is: the Meeting table contains a "foreign key" that
"references" the MeetingCenters table.

And, what we are describing now is called a "one-to-many" relationship, as
opposed to the "many-to-many" relationship that we discussed earlier (which
required a third "linking" table to create the relationship.)

I'm taking my SQL7 certification exam tomorrow, so this is good review for
me... :-)



"Jacob Hatley" <JHatley@aol.com> wrote:
>What if I each location has a specific location code -- to show where it
>is... would I have a 'MeetingCenters' table with a CenterId, address, city
>state, zip, description field and then relate that in the meeting table
for
>the location?
>
>
>"Ed Butler" <ebutler@aps-now.com> wrote in message
>news:396cb64c$1@news.devx.com...
>>
>> Jacob,
>>
>> What you are describing is a classic "many-to-many" relationship. To
>model
>> this correctly you will need to use 3 tables rather than one. Like this:
>>
>> Meeting {MeetingID, MeetingDate, Subject, Location, ...}
>> Person {PersonID, FirstName, LastName, Title, ...}
>> MeetingAttendance {MeetingID, PersonID}
>>
>> This would be easier to understand w/ an Entity-Relationship diagram,
but
>> that's king of hard to do in plain text format...
>>
>> To answer the question: "Who attended the recent Finance meetings" you'd
>> use a query like this:
>>
>> select p.FirstName, p.LastName, m.Subject, m.MeetingDate
>> from Person as p
>> inner join MeetingAttendance as ma
>> on ma.PersonID = p.PersonID
>> inner join Meeting as m
>> on m.MeetingID = ma.MeetingID
>> where m.Subject like '%finance%'
>> and m.MeetingDate > '07/01/2000'
>>
>>
>> Hope this points you in the right direction...
>>
>> --Ed Butler
>>
>>
>> "Jacob Hatley" <JHatley@aol.com> wrote:
>> >Hi
>> >
>> >I'm new to relational database design and I was wondering if someone
>could
>> >help me with the following problem:
>> >
>> >I have to create a database that has to account for meetings that are
>held
>> >and all the information pertaining to those meetings. So let's say we
>create
>> >a table called 't_meetings'. In this table you would have fields like
>> >member_id, etc... My question is if I design a "meeting" table, every
>> >record in that table is going to represent 1 meeting -- this is what
I do
>> >*not* want because I must have duplicate meeting ID's since many members
>> can
>> >attend the same meeting - so having a meeting id as the primary key would
>> >not work. What I want is a design that will allow me to get information
>> >about different members of a certain meeting (like who attended, and
what
>> >they bought, etc.) -- I need a better way of thinking about this.
>> >
>> >IOW, if you had to create a table that is used to track what happens
at
>> a
>> >meeting, how would you go about it?
>> >
>> >Does this make sense? Please, any ideas are helpful
>> >
>> >thank you
>> >
>> >
>>
>
>