[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

data order schema

Posted on 2004-11-30
10
Medium Priority
?
158 Views
Last Modified: 2013-11-19
Hi,
We have an org chart in which the levels are currently organized alphabetically.

We would like to make this user adjustable and would like to do so in a way that is easy for users and fast for the database.

What would be an efficient schema for organizing the display order.

The order entry will have to occur in an automated process.  With the record id of the node the user has clicked and the record id of the node the user is putting it beside.

The schema has to allow the user to adjust the order an unlimited number of times.
0
Comment
Question by:mcm-mgm
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 12709264
Do not tie record id's to display order.  It should make no difference in what order the data exists inside the database.  Instead, use an ORDER BY clause when selecting data.

SELECT * FROM someTable ORDER BY userName;
SELECT * FROM someTable ORDER BY userLevel;
0
 

Author Comment

by:mcm-mgm
ID: 12709581
Currently we use order by entry_name
The provision of the record id is for editing the data record to accomodate a change in the order.

So far we have considered using an integer system and a system which appends a modifier to the order by entry_name.

We like the append approach since it defaults to alphabetical and had considered doing orderby (order+entry_name)
however we are not sure that this is the best approach and, if it is we are not exactly sure how to accomplish it if the nodes are reorganized repeatedly.
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 12709647
Can you post some sample data and explain a couple of ways you might want to order it?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:mcm-mgm
ID: 12710196
1,2,3,4,5,6,7,8,9,10
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12713746
Please post here the table structure.
0
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 12714557
I do this kind of thing quite a lot, and I use a separate 'orderby' field. For example if I have a table of menu items that I want to appear in a particular order, I just assign them sequential orderby values. Their ctual values don't matter, just their relative magnitude. So a typical query to fetch a menus' contents might be:

SELECT * FROM menuitem WHERE menu_id = 23 ORDER BY orderby, name

I usually use a byte field (tiny int) which limits it to 256 items, but that's usually sufficient, and I make them default to 100, so they will be sorted by name unless I change the values. This also makes it easy to push particular items to the top or bottom of the list, but otherwise don't care.
0
 

Author Comment

by:mcm-mgm
ID: 12718731
Thanks.  Intuitively there is an elegant solution to this which will make me say "why didn't I think of that".  Maybe we will see it yet?
0
 

Author Comment

by:mcm-mgm
ID: 12729999
I was hoping to learn that there was a database function called something like "After that one" that would daisy items together.  And when a position is changed it just shifts everything along instead of having to rewrite a thousand records.
0
 
LVL 25

Accepted Solution

by:
Marcus Bointon earned 1600 total points
ID: 12734058
You really shouldn't worry about that. The internal ordering of records is pretty much irrelevant. How do you propose to 'just shift everything along' without writing to every record anyway? It's also unrealistic to expect the database to know what 'after that one' means anyway - the database should not have to (and cannot anyway) understand your data. One advantage of the mechanism I proposed is that you don't necessarily have to rewrite every record, and usually worst case you only have to update the items in a single menu (it doesn't matter if orderby values appear more than once in different menus - they're only meaningful in the context of a single menu).
0
 

Author Comment

by:mcm-mgm
ID: 12738207
What would the php look like that would organize the dataset in this way?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
Suggested Courses
Course of the Month20 days, 13 hours left to enroll

864 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