03-26-14, 20:42 #1Registered User
- Join Date
- Mar 2014
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...
03-27-14, 09:37 #2Resident Curmudgeon
- 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:
- Locations: Where something happens (in your case a city)
- Events: A location with a date, like London on Jan 15
- People: A person, like Bob
- Attendance: A person at an event, like Bob at the event in London on Jan 15
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.