sorting based on various data types

[Josh]

I've been tasked with designing a database to store collective
agreements. I need to store each sub-article of each article of each
agreement in it's own row within a table. I'm having difficulty
figuring out the best way to store each sub-article's identifier so
they sort properly.

The majority of the collective agreements use numbers in a dotted
notation, for example:

1.1
1.2
1.3.1
1.3.2
1.3.3
1.4

Some agreements mix letters with numbers, such as:

A-1.1
1.1.A.1

Any ideas how I should deal with this?

[Robin Tucker]

Use fixed length fields for each number in your "path string", ie:

0001.0001
0001.0002
0001.0003.0001
0001.0003.0002
0001.0003.0003
0001.0004
0A-1.0001
0001.0001.000A.0001

Converting the path is simply a case of removing leading "0" after each
fullstop. This should sort correctly.
"Josh" <jp****@caut.ca> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I've been tasked with designing a database to store collective
agreements. I need to store each sub-article of each article of each
agreement in it's own row within a table. I'm having difficulty
figuring out the best way to store each sub-article's identifier so
they sort properly.

The majority of the collective agreements use numbers in a dotted
notation, for example:

1.1
1.2
1.3.1
1.3.2
1.3.3
1.4

Some agreements mix letters with numbers, such as:

A-1.1
1.1.A.1

Any ideas how I should deal with this?

[Josh]

That's much simpler than the ideas I had. Thanks!

[--CELKO--]

You should be using a document management tools for documents. And
this is coming from an SQL fanatic!

[Josh]

We're planning to create a system that will compare articles from many
different agreements and will link articles and sub-articles to a
subject index. Users will then be able to pull up contract language
from various agreements that deal with the specific subject they are
interested in. We have a document management system here, but it
certainly can't do this. Are you aware of other document management
tools I should look at?