MySQL table design - refer to multiple foreign rows

Question

Minimilized background (ie in bare pseudo code details)

I am making a record keeping (among other things) php/mysql app for my farm. There are lots of types of animals etc that could have pictures (or other records - videos etc.) but just for simplicity I'll only refer to one of each (Goats and Pictures). so say the

tables are approximately like so:

CREATE TABLE BMD_farmrecords_goats (
      goat_id INT NOT NULL AUTO_INCREMENT,
      goat_name TEXT,
      ...more columns but whatever, unimportant...
      primary_key(goat_id))

CREATE TABLE BMD_farmrecords_pictures (
        media_id INT NOT NULL AUTO_INCREMENT,
        media_name TEXT,
        media_description TEXT,
        media_description_short TEXT,
        media_date_upload DATE,
        media_date_taken DATE,
        media_uploader INT, //foreign key constrained to user table but unimportant for question
        media_type ENUM('jpg','gif','png'),
        media_hidden BOOL,
        media_category INT, //foreign key constrained to category table but unimportant for question
        PRIMARY KEY  (media_id)

So the problem(s):

Obviously a picture could have multiple goats in it so I can't just have one foreign key in picture to refer to goat.
there are more than one livestock tables that would also make that a poor choice but not worried about that right now
Basically no optimization has been applied as of yet (ie no lengths set, using TEXT rather than varchar(length)) etc; I'm not worried about that until I populate it a bunch and see exactly how long I want everything.

so the question: what is the best_ way to link a picture to multiple goats (in terms of A) best performance B) best code conformance to standards. I'm thinking I'll have to do an extra table:

create TABLE BMD_farmrecords_goatpictures (
   id INT NOT NULL AUTO_INCREMENT
   picture_id INT //foreign key to BMD_farmrecords_pictures->media_id
   goat_id INT//foreign key to BMD_farmrecords_goats->goat_id

So is there any better way to do that?

Of course with that method I'll probably have to change *_goats table to be a parent *_animals table with then a type field and reference animal_id instead but I'm not worried about that, just about whether or not the extra table referencing both tables is the best method.

thanks;

the extra association table approach seems about right. This will help normalize your database. — Maximus2012, Jul 23 '13 at 15:32
you can even do away with primary key for BMD_farmrecords_goatpictures table and make picture_id, goat_id as a composite primary key. — Maximus2012, Jul 23 '13 at 15:33
additionally, before finalizing on the database design, you might want to run some sample queries that would give you the data that you would need and make sure that your db structure satisfies your query requirements. — Maximus2012, Jul 23 '13 at 15:34
The link table (as you have here) is the best solution. There are other possibilities but they are generally quite hideous and would cause major future problems (for example having a comma separated list of the animals in the pictures table). — Kickstart, Jul 23 '13 at 15:38
a composite primary key? hmm hadn't heard of that term before. Okay just quickly doing some reading and on http://stackoverflow.com/questions/5835978/how-to-properly-create-composite-primary-keys-mysql read "...both columns should be unique,..." if that is the case it wouldn't work - do you know if that is the case? — Nick Dickinson-Wilde, Jul 23 '13 at 15:39
Yes, this is a many-to-many relationship. A goat could be in multiple pictures, and a picture could have multiple goats. The standard way of implementing this is with a cross reference (or xref) table, just as you have done. Using a composite primary key is also standard practise, although there is nothing at all wrong with using a separate PK as you have done. — criticalfix, Jul 23 '13 at 15:40
For a composite primary key, the _combination_ of both columns should be unique. That should be workable here, unless you are worried about a situation where your app might try to add the same picture, with the same goats, to the xref table twice. That will fail on a PK constraint violation. — criticalfix, Jul 23 '13 at 15:42
okay, great thanks everyone. I thought that was the logical way to do it but since this will have a fairly large (comparatively speaking) dataset (among other things it will have a report on each animal each day (even if that report is 'nothing to report')) I want to do it right the first time. — Nick Dickinson-Wilde, Jul 23 '13 at 15:42
If the app tries to mark the same picture as having the same goat twice I'd call that a failure in my app design (or crazy user) so I'm not worried about that - at worst I can put in a catch for a user trying to do that kinda crazy thing — Nick Dickinson-Wilde, Jul 23 '13 at 15:45

score 0 · Accepted Answer · answered Jul 29 '13 at 15:50

From the discussion just changing my original idea to use a composite primary key:

create TABLE BMD_farmrecords_goatpictures (
   picture_id INT //foreign key to BMD_farmrecords_pictures->media_id
   goat_id INT//foreign key to BMD_farmrecords_goats->goat_id
   PRIMARY KEY (picture_id, goat_id))

MySQL table design - refer to multiple foreign rows

tables are approximately like so:

So the problem(s):

1 Answers1