Database Structure help

[crazyrat25]

Hi, I have been given the task of teaching the database unit to my 6th form class but have very little knowledge of advanced databases. We are given a brief and then have to work out a solution to it. Once I have the structure in place I am fine with creating things like invoices and so on in Access.



I have included screenshots of the brief and my proposed solution but I think there are issues with my structure. I would really appreciate it if someone could have a look at this for me and give some feedback?

Many thanks

[John_G]

Hi -

Not a bad start, but a few things to consider:

- Performers are not related to invoices - they are related to productions, and more specifically to one specific Tour of a production.
- You did not account for the possibility of more than one tour of a production (suggests another table may be needed)
- The relation between Production and Invoice is not required; an invoice is for a booking
- I don't see anywhere that payments are tracked (date, amount paid, etc.)
- I think "Price" should refer to a booking, not the Production - different bookings of the same production could have different prices
- Some of the tables need more detail, e.g. bookings need a performance time since there can be more than one per day

These should get you started. What you need to do is go through your briefing again to ensure that you have identified all the entities ( = tables) you require, and that your proposed structure properly implements the relationships described (or implied) in the briefing.

[June7]

This is going to get complicated fast.

tblPerformers

tblProductions

tblProductionRoles

tblTours

tblPerformersTourRole
(perhaps this is where a field for backstage pass ID would go)

tblClients

tblBookings

tblInvoices

tblReceipts



Watch out for circular relationships: http://www.codeproject.com/Articles/...atabase-Design

[crazyrat25]

Thanks very much. This helps although I think the 2nd system may be too complex for an A-level project. (Exam board have hinted that it should be 5 tables - should have mentioned that originally).

I'll try this today and post what I get.

Thanks again.