mcm-mgm
asked on
data order schema
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.
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.
ASKER
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.
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.
Can you post some sample data and explain a couple of ways you might want to order it?
ASKER
1,2,3,4,5,6,7,8,9,10
Please post here the table structure.
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.
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.
ASKER
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?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What would the php look like that would organize the dataset in this way?
SELECT * FROM someTable ORDER BY userName;
SELECT * FROM someTable ORDER BY userLevel;