I have a general database question, I'll try to keep it short :)
I am building a social site where users can log in and add status messages (like twitter), share photos, keep a blog on site, and so on. I will refer to these as element types from now on (image, post, status message, etc) and these can all be assigned a category or a tag. The actual elements are stored in different databases, image info is stored in images, blog entries are stored in "entries" and so on.
My category system works much like wordpress, there is a "term" table which defines the terms, there is a "term_taxonomy" table which defines weather a term is a category or a tag (or something else), and there is a "term_relationship" table which links elements to tags and categories, this is where categories and tags are assigned to actual objects.
This is the table I am having trouble with. I can implement tags and categories no problem, but I expect this site to grow quite a lot, and I am not sure how best to store this information.
The "term_relationships" table in wordpress is simple because object really has one type of element a post. So the table there contains two primary keys, the object_id and the term_taxonomy_id. However, I work with many different types of elements, so while I have posts, I also have images and other stuff as I said. therefore I need three primary keys.
The table is set up and has element_type_id, element_id and term_taxonomy_id. When I was doing the layout diagram however I was unsure if this is a good method. The element type id can be linked from another table as a 1-N relationship, and so can the term_taxonomy_id from the term_taxonomy table. However the element ID cant be joined with any table since the table it gets its ID from depends on what the element type is.
My question is, is this ok? I can devise an ID system where I don't need to know the element type because different elements can not have the same ID, but then I can not use auto increment and it would not solve the problem mentioned above because I still can't join the element_id in the term_relationships table to any other ONE table.
I am not a huge database expert as you can see. I am quite good now in MySQL and I can get anything I would like to work, but I am not at all experienced in best practices, so any thoughts would be appriciated.
Thank you everyone!