Normalised DB and constraints

girish_mu · #1 (**permalink**) 03-20-08, 05:02

Hi! I am making a database application around a reservation system for a company that offers reservation of tickets for several events.
The 3NF i've come out with is as follows:

Code:

Customer (CustomerID, FirstName, LastName, Street, City, TelNo)
Event (EventID, EventName, EventType, Organiser, Venue, Date, Time)
Ticket (TicketID, EventID, Class, Price, Issued, Reserved, Sold) [EventID is FK]
Reservation (CustomerID, TicketID, NoOfTickets, Date, ConfirmDate, TotalPrice)

Is it correct or should I make another table for Organiser?

Concerning the constraints,what should I do if a customer or a type of ticket is deleted? Should I delete the relative info from the Reservation table?

pootle flump · #2 (**permalink**) 03-20-08, 05:08

First one - depends - do you record any info about the organiser? What goes in that field (e.g. an ID, company name etc)? Does your app require a list of organisers to select from?

The latter depends on business requirements. If you need to delete a customer entered in error then yes. If you need to remove customer details because, for example, of data protection issues then I prefer to leave the records and clear off all the attributes (name, dob, gender, address etc) leaving just an ID. That way you retain reservation info.

girish_mu · #3 (**permalink**) 03-20-08, 06:02

For the Organiser, I am recording the name of the company. Will it be better if I assign each of them an ID in another table and then link it to the Event table?

Internet Services

Web Development

Digital Marketing

Consumer Tech