6 replies to this topic

[greengohome]

Hello, everyone! I am a complete newbie to SQL Databases and I need some help.


I am trying to create a table in which each item can relate to one or more other items in the table.

For example:

ID Word Flags
1 a 2
2 b 1,3
3 c 1,2,4
4 d 2,3


This would mean that word a is related to word b; word b is related to words a and c; and so on.

What I do not know is how to write those flags in the database so that I can easily sort them by any of the flags. For example, requesting words related to word c would return b and d, as they both have flag 3.


Now, I really wonder which is the best way to achieve this. Should I just write all flags in a text string separated by commas? Or should I add a new column every time a word receives a new "flag"? Or perhaps you could think of a more convenient way of storing this data?



Thank you in advance. If I haven't been clear enough, please, ask me to rephrase

[WingedPanther73]

I would create a second table, relation_flags, to store the relations. Each record would just be two IDs that are related.

[BlackRabbit]

Wingedpanter is correct,
still , if what you want the flags for is for grouping, it would be better if you use a boolean field for each flag, it would make a more organized table

if what you want is to have a single field with accumulative flags as you shown you could pick one of this 2 options :

- use bitfields ( if your DB engine supports it )
- use a single character as flag, like you did, no comma necessary, and query it using likes

what are you pointing to do ? do you need it all to be in a single table ?

[greengohome]

Thank you both for your replies. To answer BlackRabbit's question, no, I do not need it to be in a single table. A secondary table for flag relations, as both of you suggestes, would be in order.

Neither do I know if my db can support bitfields, nor am I sure whether I would manage to deal with them from what I gathered by reading between the lines. On the other hand, LIKEs are probably exactly what I was searching for, yet completely unaware of their existence.

Now, I will try both ideas and work a bit with each to see which one applies better to my project and which one will make the sorting process faster.



Once again, thank you

[Luthfi]

I would say the best and fastest solution is to use another table to store the relationships, like WP suggested.

[VNFox]

I am amazed who ever comes up with this design. Trying to take a shortcut, but will hurt you in the long run. I would suggest the second table to show relationship. If you're doing through query it's pretty tough, but if you know how to program ... you can load all the items then Hash Them ... for each element you create a HashItem .... it's O(1) time complexity.

[gregwarner]

In regards to WingedPanther's idea, in relational model theory, this is what's known as a join table. A join table, or junction table, bridge table, or map table, as they're sometimes called, is used to implement a many-to-many relationship between two entities. (Or in your case, a many-to-many relationship of an entity to itself.) The join table usually has two fields, which are both foreign keys referencing the identifying fields of the entities you wish to create a relationship between.

In your case, you have a many-to-many relationship of an entity to itself. What that means is, one word may be related to many other words, and each of these many different words may be related to the same word. This can't be implemented sufficiently with only additional fields in the 'word' entity table. The only way is through a join table.

So in your example above, word 'a' is related to word 'b', so you add a row to the join table that links word 1 to word 2:

+----+----+
| w1 | w2 |
+----+----+
| 1  | 2  |
+----+----+

Keep in mind, this describes a one-way relationship; that is, 1 is related to 2, but 2 isn't necessarily related to 1. (Unless you write your logic to search from both sides, but this next part is a better solution: )

To add the relationships for word 'b', you'll simply add a row for each relationship represented, like so:

+----+----+
| w1 | w2 |
+----+----+
| 1  | 2  |
| 2  | 1  |
| 2  | 3  |
+----+----+

At this point, you can pretty well figure out what to do for the rest.

Anyway, not that I'm providing any new information here that wasn't already said, but I thought I'd explain a little of the theory behind why it's done that way. Hope it helps.