Database table help

[hargi]

Looking for some help here.

I have 4 models
Image, Section, Page, Post

An image can belong to only one of the Section,Page or Post.
A Post could have multiple images.

First though was save a foreign_key id in the Image table however this wont work as a post can have an Id of 1 as can a Page

Second go was with help from people was to create a linktable with a look up
Tags
Section = 1
Page = 2
Post =3
Image = 4
___
ItemLookup
|id|parent_id|parent_tag|child_id|child_tag|

This way we can then add things at a later date live videos etc.

However have been told this is wrong.

New though is in the Image table add an parent_id and parent_type id fields. This is the only other solution I can see at the moment.

Thoughts?
Thanks

 

[hargi]

Thanks.
I was writing the code to return the child items with that solution when I was told that there is a better way.

Because I have to do a query on 3 fields in the lookup table
eg

'select' => 'child_item_id',
                'where' => 'parent_item_id = :parent_item_id
                      AND parent_tag = :parent_tag
                      AND child_tag = :child_item_tag',

This will make things slow ?

In this instance I have to care about this opinion as this is a senior telling me

 

[hargi]

Thanks,

In my images table I am storing id, filename, original_filename, extension, alt_text, caption etc (from that information I can then build a path to where the file lives on the server)

The other reason was if you look at the database then it doesn't read nice. eg what is parent_item_tag 3 for example?

Yes I am doing one query to then use in another

$childrenIds = ItemLinkage::find('all', array(
                'select' => 'child_item_id',
                'where' => 'parent_item_id = :parent_item_id
                      AND parent_item_tag = :parent_item_rag
                      AND child_item_type_id = :child_item_type_id'.

$childrenIds = array($childrenIds);

$images = Image::find('all', array(where => 'id in' =: $childrenIds);

They are big on OOP and best practices, extendability etc. This is for a very small project but because I know how they think I am trying to make it how I would for a larger one.

I see your solution with the extra 3 tables but then Im guessing I would get back what makes a SectionImage different to a PostImage whene the tables are identical except for the name and the xxx_id. And if we add another module eg notes etc we would have to add a NotesImages table as well

 

[hargi]

Thanks both of you.

I am unsure which to choose as all will work.

Can I ask @AHarvey will I get a Normalisation argument thrown back at me with your solution ?
I'm not questioning your way as I agree it will work no problem but we have a purist here.

 

[hargi]

Thank you, Will check out that book looks like it could be useful.
Yes basically from what I see I have to argue my reason for whatever I write. Even though I have never seen any of his code or him do any coding.