Database design question


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Database design question

  1. #1
    Jacob Hatley Guest

    Database design question

    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



  2. #2
    Ed Butler Guest

    Re: Database design question


    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
    >
    >



  3. #3
    Jacob Hatley Guest

    Re: Database design question

    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
    > >
    > >

    >




  4. #4
    Jacob Hatley Guest

    Re: Database design question

    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
    > >
    > >

    >




  5. #5
    Ed Butler Guest

    Re: Database design question


    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
    >> >
    >> >

    >>

    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center