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

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

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

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.

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?

OK. I will ....

Leave it with separate tables.


Thanks for the advice people. Much appreciated.

Mike

if you are going to keep the separate tables, please at least put the foreign key in the right one

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

oh, man, in that case, you really must implement the many-to-many structure right away