join from variable table (polymorphism?)

[eluser]walrusk[/eluser]
I've been struggling for this for a while now but to no avail

I'm trying to allow files in my system to have attachments which are then stored in a different table. Problem is there are different types of attachments, each with their own table. The relationship is 1:1, so all the attachment table stores is the file_id and the content. The file table stores information about the file as well as a "content_type" field which contains the name of the attachment table. I'd like to be able to grab data from both tables in one query.

Is this possible?

This is the code I tried (it doesn't work) but I think it illustrates what I'm trying to do:

SELECT * FROM files
INNER JOIN files.content_type ON files.id = files.content_type.file_id
WHERE id = ".$file_id

Help?

[eluser]jedd[/eluser]
[quote author="walrusk" date="1265167562"]
I'm trying to allow files in my system to have attachments which are then stored in a different table. Problem is there are different types of attachments, each with their own table.
[/quote]

You are correct. This is the problem.

Show the schema, to be sure, but I'd suggest you need to have a table for attachments, that includes a type - the type being a FK into a type table.

That way you don't need a new table for every type of file - just a new row in the type table.

[eluser]walrusk[/eluser]
I think see what you're saying but won't I just run into the same problem? Won't I then need to join three tables? (file table, attachment table, type table)

Part of my schema is below. The reason I have separate tables for different attachments is because they store different information. A text attachment has a text field while an image attachment has a path field (varchar).

Is there something I'm missing? I'd like to have just one table for attachments as you suggest, but can I achieve this and still store different types of data?

table: files
id
user_id
title
content_type
created
updated

table: text
file_id
content

table: image
file_id
path

Thanks for your help mate

[eluser]jedd[/eluser]
[quote author="walrusk" date="1265171098"]
Won't I then need to join three tables? (file table, attachment table, type table)
[/quote]

Yes. Consistently. (And consistency is your friend.)

Part of my schema is below. The reason I have separate tables for different attachments is because they store different information. A text attachment has a text field while an image attachment has a path field (varchar).

You might want to explain what's in the text attachment. Is it the actual textual content? (If so, it's not really an attachment, is it?)

If you want to store text in a certain way - knock yourself out. But if you want to store it as a file, then do it the same way as you store your images.

You can still handle the attached files differently, based on their type - so you can echo text files (once you filter / escape them) and render image files .. etc. But store them consistently. Note that storage here is a DB concern. Handling is a programme (PHP) concern.

On that note you might want to store files based on either a UUID or their md5 / sha1 - the resultant string will play nice with most file systems, be consistently recorded, lends itself well to distributing into multiple directories (based on substr() of the filename) if you want to reduce file count in a single directory, and so on. It means you store meta data in the file-attachment table (which you should be anyway). So in addition to who uploaded it, when they did, original filename, context, description etc - you store the key (md5/sha/uuid) too.

[eluser]walrusk[/eluser]
Sorry I should have been more clear about that. Yes the text attachment is actual textual content (not a file).

Perhaps "attachment" wasn't the best choice of words; "content" would be more suitable.

Basically it works like this:

1) User clicks on "create file".
2) User either uploads an image file or enters a body of text.
3) title, user_id, etc are stored in the "file" table
4 a) If they uploaded an image, it goes in the image table
4 b) Else if they entered text, it goes in the text table

I should also mention that a file can have either 0 or 1 attachments, no more (this also makes more sense using the word "content" rather than "attachment").

I'm open to rethinking my schema, but I'm not really sure what would work best for this scenario. Thanks again for your time

[eluser]jedd[/eluser]
You intimated earlier that there were many types of attachments, but it now sounds like there are just two - text and image.

And 'text' doesn't mean file attachments, as you've just described, but actual text fields within the database.

Can you clarify - are there other types of files that you're dealing with? If not, then you only need a content (or text) table, and another table to handle images (though I still reckon you should not store the file using the filename you're given but instead make up a sane and safe one on the fly).

[quote author="walrusk" date="1265174071"]
I should also mention that a file can have either 0 or 1 attachments, no more (this also makes more sense using the word "content" rather than "attachment").
[/quote]

So a user can associate some text or an image to ... whatever entity it is that attachment refers to here.

[eluser]walrusk[/eluser]
I only have text and image attachments at the moment, but I would like to structure things in such a way that that can be expanded. I would like to be able to attach a boolean, and also files other than images (which I'll store in a generic 'files' table along with images et al).

Based on that do you think my schema as it is now is appropriate? Is it possible to do a join between a table and another variable table (the name of which is stored in the first table)? I've been googling for a while now :/ Maybe I'm not using the right terms.

EDIT: Hrm.. reading this and it seems to be similar to what I'm trying to do.

The second post lists a couple of alternatives but I'm not sure if they fit my case. Maybe the 'use two columns' approach, but then I'd have to have a foreign key in files for each type of content (file,text,bool,...); doesn't seem too elegant. Do you have any thoughts on this?

[eluser]jedd[/eluser]
[quote author="walrusk" date="1265178109"]
I only have text and image attachments at the moment, but I would like to structure things in such a way that that can be expanded.
[/quote]

Okay, good - this is how I read your first post.

I would like to be able to attach a boolean,

Can you explain this? A boolean is a binary value - a flag. What does the attachment of a boolean mean to you / why are you wanting to attach a boolean?

I read through the stackoverflow link you provided. One thing they didn't suggest was UUID'ing every value. I'm not sure how elegant this is in practice - as you have to search multiple tables in order to find the UUID you're looking for.

But I don't think your situation is as complex as the one described in that thread.

Is the text that is entered by the user - is this uploaded as a file, or entered into a form? If it's a form then yeah, keep it as you're doing it. If it's uploaded as a file, then I'd be tempted to keep it as a file (depends what you want to do with the contents, I guess, though). How many other types of files are you likely to want to handle? I know this isn't something you have the answer to - but is it a couple, ten, several tens? Do you want an approach that will not require re-visiting the database whenever you add a new type, or are you willing to change the schema & your php code on each addition of a type?

Is it possible to do a join between a table and another variable table (the name of which is stored in the first table)? I've been googling for a while now :/ Maybe I'm not using the right terms.

This is possible, but it will take a couple of calls. In one of my projects (and I'm far from confident I've done this right) I have about 30 tables that all start with taxa_ - there's one for every part of the taxonomy (taxa_species, taxa_genus, etc). I have another table that is an index of all those tables - useful for applying hierarchy in my case. Also it needs to cope with elements of the taxonomy that aren't there yet, and/or don't apply to a particular organism.

As I say, though, your situation isn't hugely complex - it's a flat model where you're just trying to handle different types of what are essentially the same thing - that is, attachments.

Anyway, yes, you could store the table name within another table - but this is pretty ugly (takes up a lot of space, apart from anything else, and requires one query to get the name, and then another after you've munged up a new SQL query in PHP).

I sort of explained bits of what I think would be the best approach above, but I'll summarise it here.

A directory in the file system for storing attachments - $ATT - defined in a config file.
User uploads a file, and either nominates the type, or it's determined during upload (first four bytes, filename extension, whatever)
System generates an md5/sha1/uuid to associate to this file - call this the $key (fixed length, [0-9a-f], works on every file system known to man)
File is saved at: $ATT / $key
Attachment table gets a new row. Attachment table layout looks like ( id, user_id, when, original_file_name, key, att_type_id )
Attachment type table (att_type) looks like (id, tla, description) tla = 3+ letter abbreviation eg. 'jpg' , description is for user display purposes eg 'Picture'

For extra points you can provide the option - at time of upload - for the user to create a new attachment type - though of course you still need to have the php code in there to handle that type if you need to do something beyond just storing it.

And, yes, I realise this comes down to your final comment - where you suggest it's not very elegant to have a foreign key - but I'm not sure why you think fk's are inelegant in this case.