I am “designing” a database layout. I have two different tables, containing two different types of data with different column requirements. I want to be able to attach zero or more images to each of the items in either table. It is unlikely that an image would be appropriate to be linked to an item in more than one of the tables, or to more than one item in the same table.
Is it better to have a single images table, with a column for the item id to which it is associated, and another column to indicate which of the two tables it is in, or to have two images tables, one to correspond with each of the initial two items tables?
I can see both ways - having a single large table for images seems “better”, but it does seem a bit messy to have to pick out which table they refer to. Though having said that, as the images are only referred to from their associated item, I doubt it would be an issue.
I think I’ve talked myself into a single images table there, but I’d be interested to hear opinions.
Images are images. It’d be cleanest to have one image table and FKs on the other tables to the image table itself (or lookup tables if there can be more than one image per row on the “item” tables)
Two FKs in the image table? Surely the image table only needs one, plus an indicator as to which table it relates to? What would the second be for? I mentioned above that it’s unlikely that an image would link to items in both tables, sufficiently unlikely that uploading the image a second time wouldn’t be an issue.
As I think about it, there could be more than two tables that need to have images stored against their rows.
I’ve changed my code now (I had originally put a second image table, but wasn’t really happy with it) to store all images in the same table, and have a row that indicates which table it relates to. At the moment, that’s a letter “I” or a letter “G”, which means something to me. It would be relatively easy to add more letters to indicate different tables.
you could do that, but it wouldn’t be an actual FK
a foreign key references a specific table – you can’t have it reference this one or that one
and if you say, okay, it will hold the id of one of several tables, but it won’t be an actual FK, and another column will indicate which table it refers to, well, sure, but you lose something in the process
on a related topic, if you have two (or more) item tables, are you doing anything to ensure that item 27 doesn’t exist in more than one of them? you’re going to make that ‘I’ or ‘G’ letter carry a lot of weight, aren’t you
Except it’s not, because you’re not referencing from the image to the item, you’re referencing from the item to the image.
If it’s (to use r937’s structure) an I-item, your query simply pulls from (or left joins, whichever way you’re constructing it) the table WHERE (or ON) I_id = your I-item’s ID, or WHERE G_id = your G-item’s ID when retrieving for a G-item.
Your query gets no more complex than if you had one table joined.
i think i know what you meant, but “referencing” is an unfortunate word to use here, because that’s what foreign keys do, and in this instance they reference in the other direction
you meant that there’d never be a situation where you’d have a query start with an image and then join to one of the item tables – but that’s easy, too, all you do is join all of them, as only one of the FKs (even if there’s more than two) will be non-null
Yeah. Bad choice of phrase. Basically what i meant was the reference frame (use case? better phrase maybe.) for the query will be “I am looking for information about item, give me the stuff about it, including pictures”, rather than “I am looking for information about image, give me stuff about it, including items its linked to.”
OK, poor choice of terminology down to me not realising it has an actual specific meaning. As I look at your next post, I see that I’ve never used actual foreign keys before, but I have plenty of tables that refer to other tables. Something for me to read up on.
No, because they’re entirely separate / different things, and the unique id doesn’t actually matter to the item. But yes, it does mean that the I/G/whatever else letter is important.
That was my thought, I will bring up an item from either (or any, if I expand it) table and want to display the images connected to it, but I can’t think of why I’d ever bring up the images and want to work backwards to the item they relate to. Though I imagine a select/case (or equivalent) in the query might allow that, or failing that another query inside the display code. Not ideal, but unlikely to be needed.
It doesn’t seem right to have to look at a separate column to see which table the item is linked to, but it also doesn’t seem right to have a column in the images table for each table - if I add image attachments to other tables, I’d need a column for each one which doesn’t seem that different to just allowing up to ten columns in the main item table for image names. At that point I could have a separate table to link images to items, but I’d still have to indicate which item they link to.
Seems to me that whichever way I do it, there are bad things about it, and it’s perhaps just a case of picking the least bad.
Thanks for the thoughts anyway, I’m off to read up on foreign keys and see what I’ve been missing.
The simplest way to go about this I’d say is to just create two image tables. SELECT queries become easier, the table structure is easier, if you ever want to delete either I or G that would easier to (just throw away two tables instead of one table and parts of another table), the images tables can evolve separately (one may be added a description, while the other one doesn’t need it), etc.
If you really insist on having a single image table I’d use a many-to-many join tables inbetween to adhere to the open/closed principe.
CREATE TABLE I_items
( id INTEGER NOT NULL PRIMARY KEY
, foo VARCHAR(9)
, bar VARCHAR(37)
);
CREATE TABLE I_Images
( I_id INTEGER NOT NULL,
, FOREIGN KEY ( I_id ) REFERENCES I_items ( id ) ON DELETE CASCADE,
, image_id INTEGER NOT NULL,
, FOREIGN KEY ( image_id ) REFERENCES images ( id ) ON DELETE CASCADE,
, PRIMARY KEY (I_id, image_id)
);
CREATE TABLE G_items
( id INTEGER NOT NULL PRIMARY KEY
, qux SMALLINT
, fap DECIMAL(7,2)
);
CREATE TABLE G_Images
( I_id INTEGER NOT NULL,
, FOREIGN KEY ( I_id ) REFERENCES G_items ( id ) ON DELETE CASCADE,
, image_id INTEGER NOT NULL,
, FOREIGN KEY ( image_id ) REFERENCES images ( id ) ON DELETE CASCADE,
, PRIMARY KEY (I_id, image_id)
);
CREATE TABLE images
( id INTEGER NOT NULL PRIMARY KEY
, image BLOB
);
That way SELECTS are a bit easier, and if you ever need K_Items for example you just add two more tables, instead of having to ALTER the images table.
Would that alteration be to add a third column for the foreign key into K_Items? So with the way I have it now, I’d just allow the code to put a “K” into the “type” column?
I originally started with a separate table for each type of image, and figured that might not be the ideal way, and came on here to ask the question. It seems that there isn’t a “best” as you’ve now posted an opposite view to earlier posters.
Of course, the other thing with having two (or more) image tables, comes about because I have the images stored outside of the database. With a single image table I can shove them all in the same directory, but with separate tables I’d either have to have a directory for each image type, or include the image table ref into the image name to make them uniquely-named. (I haven’t mentioned before that the images are just given a name to match the unique id, and not stored as blobs in the database, so they are /mages/1.jpg, 2.jpg and so on).
and now, just to throw a spanner into the machinery…
ever heard of supertypes/subtypes? (google it, optionally add tables)
you make each of your I and G table a child of the supertype table – they can still have different columns, but they inherit their PK from the supertype table (where you could just go ahead and use an auto_increment PK as usual)
then the images in the single images table link to the supertype table