Database design

Topic

New Reply

I am developing my first database for a travel agent.
This business sells amongst other things bus tickets, accommodation and ski lift tickets.

I originally started putting all products into one table then realised that whilst these product types do have some common features (cost and selling price, supplier record) they also have some differences, for example bus tickets are good for a day, accommodation has various classes (twin vs single bed for example) as well as date in and date out, ski lift tickets also have various classes – adult, youth, and duration.

In designing the database should I still have a Products table listing all the products and only their common features and then in separate tables have the items that differentiate them (such as say RefAccom table containing room class, date in date out and in say RefLiftTicket table list ticket class, duration etc) each related back to the booking table.
OR
Should I have separate tables for each of these product types?
What is best practice?

thanks for any help.

Gavin

Reply | Quote

Replies

I guess you will quickly find out that there are people who will favor a common table for the common product data, plus a specific table for each of the product’s specific data and others that will favor a different table for each of the products. I would probably prefer the former, since it is the one that’s closest to the UML model for the problem (or the E/R model).

When you have the product data split between different tables, retrieving that data may give you performance issues, depending on the number of records and how you retrieve the records from the different tables. From a practical point of view, a union of inner joins is much better than a the use of outer joins, so have that in mind when accessing your product data, if you go with the general product table + specialized tables for different products details approach.

Reply | Quote

As this is your 1st commercial database, I suggest you employ the use of local experts. It won’t be cheap but you can build a business relationship there.

Reply | Quote

As this is your 1st commercial database, I suggest you employ the use of local experts. It won’t be cheap but you can build a business relationship there.

I agree with this advice from Browni. As a former Microsoft Access MVP (2006 – 2012), I am aware that Access MVP Albert Kallal has an application called RIDES (http://www.kallal.ca/rides/Rides.html), which stands for “Reservation Information Display Enquiry System”. You might want to contact Albert to see if a slight modification could get your desired application up and running much quicker.

Reply | Quote

Hi Gavin,
I think that the preferred database design would also depend on the extent to which the agency is anticipating its data model to change over the time that your database would be in service.

For example, imagine that the agency were to add a product line that does not share all of the formerly “common” attributes. If all of a product line’s attributes were only in a corresponding product-line-specific table, then you would need to add a table for the new product line but the other product-line-specific tables might be unaffected.

In contrast, if the schema had a distinct table for the “common” attributes, then to maintain the database’s conceptual integrity would entail removing the no-longer-common attributes from the common-attribute table and inserting them into the appropriate product-line-specific tables. This would presumably cascade to queries, views, etc. It is feasible but can become a nightmare and does not seem a sensible undertaking for someone who’s inexperienced in database design and management.

Hope this helps,
Dave

Reply | Quote

Thanks Dave,
What you are saying I think is in determining which way to go, give consideration how would it handle product line additions down the track.
Fortunately the scope of business is limited and will not add product lines which are vastly dis-similar to those that it currently sells. I anticipate that adding tables (for product lines or non common attributes) will not be a big problem.

Further, as I am only in the design phase I currently do not have the problem of deconstructing the tables for their non common attributes whilst the database is operating.

Thanks for your considered words on this.
Gavin

Reply | Quote