Database relationships
Discussion
It's been a long time since I did any DB modelling theory.
Say for example I am creating a tagging system for a blog engine, I would start off with a 'Posts' table and a 'Tags' table. (I'm thinking MySQL or MSSQL style database)
I now need a many-to-many relationship between Posts and Tags, which I've seen this implemented in different ways.
1: Comma delimited tag id's in the posts table
2: Create a link table
Am I right in thinking that the link table route is the better way to go? Or is there another new fangled way of doing things?
Say for example I am creating a tagging system for a blog engine, I would start off with a 'Posts' table and a 'Tags' table. (I'm thinking MySQL or MSSQL style database)
| PostID | PostTitle |
|---|---|
| 1 | My first post |
| 2 | My second post |
| TagID | TagName |
|---|---|
| 1 | Porsche |
| 2 | Tyres |
| 3 | TVR |
I now need a many-to-many relationship between Posts and Tags, which I've seen this implemented in different ways.
1: Comma delimited tag id's in the posts table
| PostID | PostTitle | TagIDs |
|---|---|---|
| 1 | My first post | 1,2 |
| 2 | My second post | 2,3 |
2: Create a link table
| LinkID | PostID | TagID |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 3 |
| 4 | 3 | 2 |
Am I right in thinking that the link table route is the better way to go? Or is there another new fangled way of doing things?
I think there's more to it than that.
Are you ever going to change the TagName for a TagId? If not then why have the TagId? With the 'link' table scenario changing a TagName could change the meaning of the Tag altogether and you would not necessarily want all your Posts to suddenly be Tagged with a different meaning. If you are never going to change a TagName you should look at it differently.
Post table:
Tag table:
PostTag table:
Then index the PostTag table on both columns - if you find you need to based on your anticipates volumes.
You need to consider the data, its meaning and its lifetime, not just concentrate on the relationships and normalisation.
Mike
Are you ever going to change the TagName for a TagId? If not then why have the TagId? With the 'link' table scenario changing a TagName could change the meaning of the Tag altogether and you would not necessarily want all your Posts to suddenly be Tagged with a different meaning. If you are never going to change a TagName you should look at it differently.
Post table:
| PostID | PostTitle |
| 1 | My first post |
| 2 | My second post |
Tag table:
| TagName |
| Porsche |
| Tyres |
| TVR |
PostTag table:
| PostID | Tag |
| 1 | Porsche |
| 1 | Tyres |
| 2 | TVR |
| 3 | Tyres |
Then index the PostTag table on both columns - if you find you need to based on your anticipates volumes.
You need to consider the data, its meaning and its lifetime, not just concentrate on the relationships and normalisation.
Mike
ewenm said:
cs02rm0 said:
But that way you have the flexibility to change the real world data without screwing up how you access it.
Absolutely.In my experience the client says the data is set in stone, will never change, then 6 months later they're changing the data. Keep the IDs in place.
All makes sense to me, thanks guys.
I've always had an ID field in my database tables, it just seems the safest way of doing it, and I don't expect the overhead is that great. It generally just makes your SQL statements a bit more involved.
And you're right, the most fixed of fixed data inevitable ends up needing some kind of tweak at some point. That's clients for you.
Anyhow, glad I'm still going about things the right way
I've always had an ID field in my database tables, it just seems the safest way of doing it, and I don't expect the overhead is that great. It generally just makes your SQL statements a bit more involved.
And you're right, the most fixed of fixed data inevitable ends up needing some kind of tweak at some point. That's clients for you.
Anyhow, glad I'm still going about things the right way
Not meaning to labour the point, but I will, I understand all that generalisation about changing data, entity life histories, using IDs etc etc. However the original post was specific (not a general question about holding many to many relationships) about adding tags to posts not creating a relationship between major entities.
As I understand it the OP just wants a way to add multiple textual tag attributes to a post. It is the text (i.e. TagName) of tag that is the important bit not the id of the tag in some other table. Based on the examples given you can't change the TagName for TagId 1 from 'Porsche' to 'Porsche 356' - it completely changes the meaning of the tag - that is my whole point. You will end up with all 'Porsche' posts tagged as 'Porsche 356' which would clearly be wrong!! A post originally tagged as 'Porsche' by the poster should remain that way and not change if someone else edits the Tag table. We aren't talking about things like customer references and customer names here where if a customer changes their name they are still the same customer.
Unless of course the designer has some genuine reason for making it different why complicate it unnecessarily?
GUIDs - you having a laugh?
Mike
As I understand it the OP just wants a way to add multiple textual tag attributes to a post. It is the text (i.e. TagName) of tag that is the important bit not the id of the tag in some other table. Based on the examples given you can't change the TagName for TagId 1 from 'Porsche' to 'Porsche 356' - it completely changes the meaning of the tag - that is my whole point. You will end up with all 'Porsche' posts tagged as 'Porsche 356' which would clearly be wrong!! A post originally tagged as 'Porsche' by the poster should remain that way and not change if someone else edits the Tag table. We aren't talking about things like customer references and customer names here where if a customer changes their name they are still the same customer.
Unless of course the designer has some genuine reason for making it different why complicate it unnecessarily?
GUIDs - you having a laugh?
Mike
Thurbs said:
I echo the comments about link tables. I have also moved on from ID's, especially database indexed ones. GUID is the future. Fact!
Very interesting! I say it has its place but is definitely not a total replacement of IDs, for example take your classifieds here. The performance implications would be massive for the extra 4 times storage and the execution time to join the larger indexes. Also having a readable ID for the adverts makes things much easier when dealing with enquiries, so if we have to have a unique ID then we might as well index on it too.130R said:
I'm not a DBA but ... I have worked with Oracle a lot and always use a sequence with a trigger for the ID. When refactoring you are fine because the ID is meaningless.
It all depends on what platform you're working with. Some are hash based (for the larger systems/Data Warehouses), so IDs/System generated keys are to avoided and real data should be used as the hash value.Of course before any of this is looked at a logical model is fundamental since it reflects the business.
Edited by Silver993tt on Thursday 1st April 17:14
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff