Database normalization/redundancy question

[danielpataki]

Hi Everyone!

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!

Daniel

[ShawnCplus]

Why is it you can't use AUTOINC for your primary key? I don't really get your explanation of why you can't.

[digital-ether]

I feel like I just drank a whole bottle of Vodka and then beat my head with the empty bottle.

That said..

So you have a table with:

element_type_id, element_id and term_taxonomy_id

So you are saying:

element_type_id and element_id have a direct relationship. Then just remove one of them, since it is redundant.

In this case it would be element_type_id since it would have a one to many relationship with element_id.

In other words, if you can get the element_type_id from element_id, then don't include element_type_id. The only reason why you would probably want it is for redundancy, so you don't have to do an extra join to lookup the element_type.

[danielpataki]

Hi, thanks for the responses!

I have three separate tables holding very different elements, and I can not use autoincrement in three tables at once. I can use them in each, but this will not mean that table 1 will have different ID-s than table 2. If I use my own numbering system I can not use auto increment.

In the table in question, the element_id does not identify the item for the above reasons. If an item has the element_id of 143 for example, there might be an item with that ID in the images table, and an item with that ID in the posts table. This is why I have the element type ID as well.

[digital-ether]

Hi, thanks for the responses!

I have three separate tables holding very different elements, and I can not use autoincrement in three tables at once. I can use them in each, but this will not mean that table 1 will have different ID-s than table 2. If I use my own numbering system I can not use auto increment.

In the table in question, the element_id does not identify the item for the above reasons. If an item has the element_id of 143 for example, there might be an item with that ID in the images table, and an item with that ID in the posts table. This is why I have the element type ID as well.

Ok that makes sense.

You have:

element_type_id, element_id and term_taxonomy_id

You need an auto incrementing ID on this table. Then use that ID to identify a unique element.

unique_element_id, element_type_id, element_id and term_taxonomy_id

That is actually a really good table structure. Just make sure you index all 3 columns, since they are all references.