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.
mcm-mgmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

snoyes_jwCommented:
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
mcm-mgmAuthor Commented:
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
snoyes_jwCommented:
Can you post some sample data and explain a couple of ways you might want to order it?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

mcm-mgmAuthor Commented:
1,2,3,4,5,6,7,8,9,10
0
Helena Markováprogrammer-analystCommented:
Please post here the table structure.
0
Marcus BointonCommented:
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
mcm-mgmAuthor Commented:
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
mcm-mgmAuthor Commented:
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
Marcus BointonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcm-mgmAuthor Commented:
What would the php look like that would organize the dataset in this way?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.