Solved

Best method to store ordered list?

Posted on 2006-11-05
6
334 Views
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?
0
Comment
Question by:rolandsnowe
  • 3
  • 3
6 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 17878667
Hi,

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

Author Comment

by:rolandsnowe
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.
0
 
LVL 35

Accepted Solution

by:
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.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 35

Expert Comment

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

Author Comment

by:rolandsnowe
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.
0
 

Author Comment

by:rolandsnowe
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
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now