Database relationships - 1:1 but not always?

Apologies for the fairly unhelpful title, if you have a better suggestion please feel free to edit it.

I'm using CakePHP and the bake functionality (I don't need to bake however).

What's the best way of achieving the following:

table schema:

table ranges   
id | name | description

table images
id | range_id | picture

table info (here i am confused)
id | range_id | height | width | colour

Basically, one range may have 开发者_StackOverflow社区many images (1:Many). I can show this fine.

Now, each range will have an entry in the info table (1:1) and some attributes about the range such as height, colour, width. But not always...

Let's say I have a range foo. foo has five images that all have the same height, width and colour. However, foo has one image that is a different size and a different colour.

When the attributes differ, I need to show this information with the respective image, rather than the ranges default information. So this image will need it's own entry in the info table.

Does this even make sense? Or am I going about this entirely the wrong way.

My application, in brief: (If it helps, think of "range" as a product)

• User selects a range
• User views images in the range
• User can click an image, and the information from info pops up about that range.
• Some images have different attributes, but still belong to the same range.
• How can I make this distinction and store it appropriately?

Please let me know if I can clarify further. Thank you.

---

I've needed to do this on occasion where a parent entity has a value that can get "overridden" by a child entity.

There are a couple of approaches you can take the structure being the easiest part.
consider the following structure

table ranges   
id | name | description | default_info_id

table images
id | range_id | picture | info_id

table info
id | height | width | colour

When does image.info_id have a value? There are two choices

Populate the image.info_id with the default_info_id from the parent. The user can then override it on the image

Pros

• You never need to look at the Range to figure out what the info is on the image

Cons

• you need to decide what to do when the range.default_info_id changes. does it effect the images or is it just for future

Only Populate the images.info_id when its different than the parent.

Pros

• If the parents.default_info_id changes when images.info_id is null it will automatically change as well

Cons

• you need to decide what to do when the range.default_info_id changes. Do you need to now null out any images.info_id that are now the same as the parent?

• You need to look at the rages table to figure out what the info_id is on the images when its null.

You can have several varieties of the above data structures but you'll still need to figure when to populate what. Here are two others you could consider that are valid (but less optimal in my opinion)

Info has an FK to both tables but one is always null

table ranges   
id | name | description 

table images
id | range_id | picture 

table info
id | range_id | image_id | height | width | colour

No Info Table at all

table ranges   
id | name | description | default_height | default_width | default_colour

table images
id | range_id | picture | height | width | colour