Results 1 to 6 of 6

Thread: Database design

  1. #1
    New Lounger
    Join Date
    Feb 2013
    Posts
    16
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Database design

    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

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.
    Rui
    -------
    R4

  3. The Following User Says Thank You to ruirib For This Useful Post:

    Gavin99 (2014-10-13)

  4. #3
    WS Lounge VIP Browni's Avatar
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    1,651
    Thanks
    38
    Thanked 161 Times in 139 Posts
    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.

  5. #4
    Star Lounger
    Join Date
    Jul 2013
    Location
    Murphy, NC
    Posts
    64
    Thanks
    0
    Thanked 8 Times in 8 Posts
    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

  6. #5
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Quote Originally Posted by Browni View Post
    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.
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  7. #6
    New Lounger
    Join Date
    Feb 2013
    Posts
    16
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •