Weird Unique ID

[Saso Zagoranski]

Hi,

I'm making a simple application which will store different items (in a SQL
server 2000 - MSDE database).
Each item has a unique ID in this form:

[max 2 characters][max 5 characters] / [max 4 characters] / [max 1
character] / [max 1 character]

an example:
AZ12345/1234/1/A
or
A 5/1/1/G

My question is, what would be the best way to store this in the database. A
way that I wou

Have a column for each part of this ID? Or store the entire string in one
column?

Here are my thougts on both of these options:

Option A (column for each part)
GOOD:
- easy sorting. If I wanted to sort by column2 I would just add "ORDER BY
column2"

BAD:
- All columns put together form a unique id. But sometimes some columns can
be NULL. And I'm
not allowed to do that if I make all columns Primary Keys (so they will be
Foreign Keys in related tables)
- it's not very practical passing 5 strings in the application

Option B(entire string in one column)
GOOD
- only one string in the application
- only one column

BAD
- sorting. I have to use substrings for sorting.
- sorting: e.g. sometimes column2 will be only 2 characters long, sometimes
5. So again,
problems with substrings

These are just my ideas... Please advise me on this one

thanks,
saso

 

[Nicholas Paldino [.NET/C# MVP]]

Saso,

What I would do would depend on the foreign key issue. If the foreign
keys are going to be pointing to the fragments of the id in your table (and
not the complete id itself), then I would say break it up into separate
columns. You can always use a default of '' for the columns which you think
would be null (they don't have to be, unless there is a distinction between
null and ''). You would also make these columns char columns, and not
varchar.

Then, you can create a view on the table which would create a column
which has the complete id, should you need it. Either that, or a computed
column.

If your foreign key is going to depend on the complete, constructed key,
then I would definitely store that, and worry about parsing it later. You
could always create functions which will parse out the parts of the id you
want, and store those as columns. Then you can sort on those easily.

Hope this helps.

 

[Saso Zagoranski]

Thanks for your reply,

No... The foreign keys use the entire string as an ID. So I agree...
the second option (storing complete ID in 1 column) sounds better.

My question is... how to sort when IDs can be in very different forms:

A 1///Z
AZ12345/1234/1/1

(most problematic is the first part because it's made out of two parts:
max 2 chars + max 5 chars)

I can't use substring because it will throw an error when all characters
aren't filled.

How would you do this?

thanks,
saso

Saso,

What I would do would depend on the foreign key issue. If the foreign
keys are going to be pointing to the fragments of the id in your table
(and not the complete id itself), then I would say break it up into
separate columns. You can always use a default of '' for the columns
which you think would be null (they don't have to be, unless there is a
distinction between null and ''). You would also make these columns char
columns, and not varchar.

Then, you can create a view on the table which would create a column
which has the complete id, should you need it. Either that, or a computed
column.

If your foreign key is going to depend on the complete, constructed
key, then I would definitely store that, and worry about parsing it later.
You could always create functions which will parse out the parts of the id
you want, and store those as columns. Then you can sort on those easily.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi,

I'm making a simple application which will store different items (in a
SQL server 2000 - MSDE database).
Each item has a unique ID in this form:

[max 2 characters][max 5 characters] / [max 4 characters] / [max 1
character] / [max 1 character]

an example:
AZ12345/1234/1/A
or
A 5/1/1/G

My question is, what would be the best way to store this in the database.
A way that I wou

Have a column for each part of this ID? Or store the entire string in one
column?

Here are my thougts on both of these options:

Option A (column for each part)
GOOD:
- easy sorting. If I wanted to sort by column2 I would just add "ORDER BY
column2"

BAD:
- All columns put together form a unique id. But sometimes some columns
can be NULL. And I'm
not allowed to do that if I make all columns Primary Keys (so they will
be Foreign Keys in related tables)
- it's not very practical passing 5 strings in the application

Option B(entire string in one column)
GOOD
- only one string in the application
- only one column

BAD
- sorting. I have to use substrings for sorting.
- sorting: e.g. sometimes column2 will be only 2 characters long,
sometimes 5. So again,
problems with substrings

These are just my ideas... Please advise me on this one

thanks,
saso

 

[Dave]

what i have done in the past for the maximum number of rows and smallest key
length is below
1st byte 0=9, A-Z
2nd byte 6 numbers 00000000
1
0 000000
0 000001
0 000002
1st byte changes when 2 set of numbers reach
0 99999999 sets 1st byte to 1 and then start 2nd set over
1 000000
1 0000001

you end up with 36 * 999999=35,999,964
thats quite a lot of rows...in total of 7 Characters
you could also add or use 8 character date
yyyymmdd
20051230+ some number you will never reach in a 24 hour period
day changes and reset the number part too 0000's
the 2 above samples will even order correct desc or Asc

dave

 

[Saso Zagoranski]

Thanks for your reply Dave,

However, I fail to see how this would help me in my example, since
I'm required to use the EXACT ID that I described earlier.

My question was how to sort when I have entries like this:

1///G
Z 1/1//G
ZZ12345/1234/1/1

and sometimes I have to sort by using the first column or second or third,
....

saso

what i have done in the past for the maximum number of rows and smallest key
length is below
1st byte 0=9, A-Z
2nd byte 6 numbers 00000000
1
0 000000
0 000001
0 000002
1st byte changes when 2 set of numbers reach
0 99999999 sets 1st byte to 1 and then start 2nd set over
1 000000
1 0000001

you end up with 36 * 999999=35,999,964
thats quite a lot of rows...in total of 7 Characters
you could also add or use 8 character date
yyyymmdd
20051230+ some number you will never reach in a 24 hour period
day changes and reset the number part too 0000's
the 2 above samples will even order correct desc or Asc

dave

Hi,

I'm making a simple application which will store different items (in a SQL
server 2000 - MSDE database).
Each item has a unique ID in this form:

[max 2 characters][max 5 characters] / [max 4 characters] / [max 1
character] / [max 1 character]

an example:
AZ12345/1234/1/A
or
A 5/1/1/G

My question is, what would be the best way to store this in the

database.

A
way that I wou

Have a column for each part of this ID? Or store the entire string in one
column?

Here are my thougts on both of these options:

Option A (column for each part)
GOOD:
- easy sorting. If I wanted to sort by column2 I would just add "ORDER BY
column2"

BAD:
- All columns put together form a unique id. But sometimes some columns can
be NULL. And I'm
not allowed to do that if I make all columns Primary Keys (so they

will

be
Foreign Keys in related tables)
- it's not very practical passing 5 strings in the application

Option B(entire string in one column)
GOOD
- only one string in the application
- only one column

BAD
- sorting. I have to use substrings for sorting.
- sorting: e.g. sometimes column2 will be only 2 characters long, sometimes
5. So again,
problems with substrings

These are just my ideas... Please advise me on this one

thanks,
saso