Best method to store ordered list?

Posted on 2006-11-05
Last Modified: 2008-03-06
I'm looking for the optimal method for storing/retirieving  a list of items that i allow the user to sort using a php/js/html front end.

I have a "projects" table that is used to store name, dates, abstract, etc for a project. I also need to store information regarding a static list of relevant keywords. For instance a set of 10 keywords, each with a rank chosen by the user. I assume that i should use a m-m type set up with a linking table but am not sure regarding the optimal implementation. Should I create a "keywords" table, populate it with the words, then store the sort information with the linking table?
Question by:rolandsnowe
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 35

Expert Comment

ID: 17878667

I would create a keyword table - populate it with the words and the ranking then link it to the other table.

Author Comment

ID: 17878694
Hmmm, I don't think I can store the ranking info with the keywords table, as there will be many projects each with their own individual ranking of keywords.

What I was thinking was having the keywords table, then in the linking table have the "projectsid" and "keywordsid" column, then have another column called "rank" or something that would store the rank for that particular keyword as it relates to the project.

Does that make sense? It seems like there must be a better way.
LVL 35

Accepted Solution

Raynard7 earned 250 total points
ID: 17878703
Well if the rankings are dynamic - have a ranking table, a keyword table and a project table.  then have one table that links all three together.
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 35

Expert Comment

ID: 17878704
that way you can have as many or as few key words as you like

Author Comment

ID: 17885709
This is my first time posting a question, is it possible to maybe post a sample sql statement that would work for a join here, I know I already accepted the answer above, and i appreciate the help.

Author Comment

ID: 17886415
Nevermind about the sample, I just realized I was being foolish to begin with, I only need two tables with a third join I can store the rank info in an extra column in the join table. thanks

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question