No, you shouldn't merge the 3 tables.
- For a given flight, aircraft may change
- For a given aircraft, flight may change
However, I'd suggest that your model isn't complex enough
- FlightSeats depends on Aircraft model (seat layout etc)
- Aircraft departs Airport too...
- .. but Flights leave and departs Airports. Or is it FlightAirport?
- Flight is arguably 2 entities: the base flight (number, schedule etc) and the actual FlightInstance (date etc)
Have you written down all your facts first? Say in tabular format like this MSDN example (actually for ORM which I use because ERDs don't capture all the information you need)
Edit, more...
- As per @ypercube and @Simon Righart comments below
- From @Simon Righart: A given aircraft might have three different classes of seats (economy, business + first) with different numbers of each each flight number might be flown by multiple aircraft
Usually, events are not infinite. There's more than one way to represent this in your database: either store an endTime
, or a duration
. You could make it optional if you don't want to force it on people, but if the event starts at 4 pm and someone shows up at 11 pm they will be disappointed that they missed the event, and your app was not obviously clear enough that the even ended at 9 pm. You might become the subject of their criticism (whether or not it's rational is a different matter).
You might also want to add a notes
field that's just some text that anyone can add for special info for the event.
Also, this design does not allow for One Event Can Host Many Book Authors. From your diagram, it looks like one event can have exactly one author. You might need a new table such as this:
event_authors
-------------
event_id
author_id
If you make this table have a composite key of event_id
and author_id
, it will allow you to have multiple records with the same event and different authors. Of course, you'll have to remove the author_id
reference from events
.
As for One Author Can Only attend One Event at a time., this one is a little trickier. Your current schema does not enforce this, but I suppose you could do it in your application. Or you could have an on-insert trigger on event_authors
that first checks that the time duration of the new event for this author does not overlap any other events for the same author for different events.
Best Answer
Start with learning third normal form
http://en.wikipedia.org/wiki/Third_normal_form.
The key part of the third normal form is that every non-key attribute should rely on the key.
For example, fIrst name and last name should only be present in one table so as not to repeat this information for every row in which they attend a presentation. Keep separating attributes out until there is not data repeated in more than one place.
If you program, you might know the adage: DRY or Don't Repeat Yourself. This holds true for data as well as for programming as if you need to change one of the attendees names, you don't want to have to find and replace it in more than one place.