Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2006
    Location
    Liverpool, UK
    Posts
    6

    One to one tables a waste of time?

    I am modelling shops and their adverts. Every shop is unique and every shop has 1 advert associated with it. Its a 1 to 1 relationship.


    I had come up with the following tables (simplified version)

    table shops
    id
    name
    location
    advert_id

    table adverts
    id
    type of advert
    text of advert

    When I got round to coding it I have to choose a shop, look up its advert_id, go to the advert table and access the data. But as it is 1 to 1 the shopid and the advertid are always the same so I'm wondering what is the point of having advert_id as a key in my shops table. Why not just use shop_id. But if I'm doing that why have an advert table at all. Should I just put the advert type and text in the shop table.

    Is the answer that if I need to change the design in the future separate tables would be more flexible?

    Mike

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Ginger The Cat
    Is the answer that if I need to change the design in the future separate tables would be more flexible?
    yes



    you said "every shop is unique" so i think it unlikely that you will ever have an advert that covers two shops

    however, it's not unlikely that you might want to have a shop with a second advert

    therefore the relationship should be one-to-many from shop to advert

    therefore the foreign key should be in the advert table

    remove advert_id from the shop table, and add shop_id to the advert table

    i.e. basically switch your one-to-one relationship around, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2006
    Location
    Liverpool, UK
    Posts
    6
    It just seems odd having inefficient code just to handle potential design changes in the future. The current requirements are clear, unique shops having 1, only 1 and always 1 advert.

    It is possible that in future the requirements might change to require 1 shop to have many adverts but it is also possible that a shopping chain for example may want to standardise on the same advert at each of its shops or standard adverts may be created for use by more than 1 shop e.g. Buy One Get One Free Today Only!

    So why should I try and guess the future. Wouldn't it be best to code for today.

    Mike

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    If it is really true that "shops [have] 1, only 1 and always 1 advert" (for ever and ever, amen, so help me Codd) then you could certainly merge the two tables - it would serve no purpose to keep them separate.

    So why should I try and guess the future. Wouldn't it be best to code for today.
    This is a cost-benifit analysis issue: how much would it cost you now to prepare for possible future needs? (very little); how much it would it cost to change it later when all sorts of code, reports etc. have been built on the current model? (much more); how likely is the requirement to change?

    Of course you can't predict everything that might change, but the idea of a shop having 2 adverts at some point in the future doesn't sound outlandish, does it?

  5. #5
    Join Date
    Feb 2006
    Location
    Liverpool, UK
    Posts
    6
    OK. I will ....

    Leave it with separate tables.


    Thanks for the advice people. Much appreciated.

    Mike

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you are going to keep the separate tables, please at least put the foreign key in the right one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2006
    Location
    Liverpool, UK
    Posts
    6
    But I don't know which is the right one.

    I suspect in my application that many shops to one advert is slightly more likely than the other way round with many to many being a possibility.


    Mike

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, man, in that case, you really must implement the many-to-many structure right away

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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