Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2014

    Question Multiple events, multiple dates, multiple attendees

    I am attempting to create my first 'proper' relational database, and have hit a bit of a brick wall in the design phase.

    The main chunk of the database will be devoted to managing the attendance of traders at various markets. Hopefully this is something that is not too difficult to achieve (for an experienced database designer - unlike me!).

    I've managed successfully to set up a many-many join via a junction table from the marketID and the producerID, so I can query a table and find out that "Bob will be at London market" using this:

    SELECT "prodCompany", "markets"."marketName" FROM "producers" JOIN "marketProducerJunction" ON "producers"."producerid" = "marketProducerJunction"."producerID" JOIN "markets" ON "marketProducerJunction"."marketID" = "markets"."marketID" WHERE "markets"."marketID" = 7 ORDER BY "prodCompany"

    This database is shown in attachment "existing.gif".

    But taking this a step further to "Bob will be at London market on Jan 1st, Mar 2nd and April 4th, then in Paris on May 1st and 7th" is frying my brain. Should I create a separate table for each market with one column - just its dates? Or should I create something like the leftmost table in the attached database.jpg - with all that nasty repeating data?

    The three variables (traders, markets and dates) can be in pretty much any combination - it's even possible that Bob could be at London and Paris on the same day (it's really Bob's Cheeses and Bob might have enough staff to do two markets at the same time).

    The data I'd want to pull out would be:
    Give me a list of everyone at London on Jan 15th
    Give me a list of ALL the markets that Tom attended

    I've attached a (probably useless) sketch of what the tables might be (database.jpg).

    I know it's a lot to ask - I've only just shown up here, but any help would be greatly appreciated! I'm assuming this isn't anything too out of the ordinary - it's like scheduling meeting rooms on different dates for members of staff. Probably...

    Many thanks,

    Attached Thumbnails Attached Thumbnails database.jpg   existing.gif  

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    I'd push the normalization of your data one step further.

    Because your events are time specific (so London only happens on a few dates instead of every day or week), I see four distinct objects that your database needs to track:
    1. Locations: Where something happens (in your case a city)
    2. Events: A location with a date, like London on Jan 15
    3. People: A person, like Bob
    4. Attendance: A person at an event, like Bob at the event in London on Jan 15
    As a side note, I'm moving your message thread from New Members forum to the Database Design forum. It should get a different set of readers, and they may offer more specific insight than this question would get in the New Members forum.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts