Jump to content

Check out our Community Blogs

Register and join over 40,000 other developers!

Recent Status Updates

View All Updates

- - - - -

Database Sorting

sql database

  • Please log in to reply
6 replies to this topic

#1 greengohome


    CC Lurker

  • New Member
  • Pip
  • 5 posts
  • Programming Language:C++
  • Learning:C#

Posted 21 July 2012 - 02:18 AM

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 :)
  • 0

#2 WingedPanther73


    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 21 July 2012 - 09:01 AM

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

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/

#3 BlackRabbit


    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts
  • Location:Argentina
  • Programming Language:C, C++, C#, PHP, JavaScript, Transact-SQL, Bash, Others
  • Learning:Java, Others

Posted 21 July 2012 - 09:20 AM

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 ?
  • 1

#4 greengohome


    CC Lurker

  • New Member
  • Pip
  • 5 posts
  • Programming Language:C++
  • Learning:C#

Posted 21 July 2012 - 09:48 AM

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 :)
  • 0

#5 Luthfi


    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 21 July 2012 - 09:18 PM

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

#6 VNFox


    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 648 posts
  • Programming Language:C#, PHP
  • Learning:Assembly

Posted 10 August 2012 - 12:50 PM

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.
  • 0

I don't just develop software. I find solutions to your business needs.

#7 gregwarner


    Obi Wan of Programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1586 posts
  • Location:Arkansas
  • Programming Language:C, Java, C++, C#, PHP, Transact-SQL

Posted 10 August 2012 - 01:12 PM

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.
  • 2

Hofstadter's Law: It always takes longer than you expect, even when you take into account Hofstadter's Law.
– Douglas Hofstadter, Gödel, Escher, Bach: An Eternal Golden Braid

Also tagged with one or more of these keywords: sql, database

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download