sorting based on various data types

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?

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" <jpiche@caut.ca> wrote in message
news:1119356513.427410.173150@z14g2000cwz.googlegroups.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?
>

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

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

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?