Weird Unique ID
Saso Zagoranski
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]
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]
- m...@spam.guard.caspershouse.com
"Saso Zagoranski" <saso.za...@guest.arnes.si> wrote in message
news:dmnpuf$fbj$1...@planja.arnes.si...
Saso Zagoranski
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
"Nicholas Paldino [.NET/C# MVP]" <m...@spam.guard.caspershouse.com> wrote in
message news:eFgMfDs9...@TK2MSFTNGP12.phx.gbl...
Dave
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" <saso.za...@guest.arnes.si> wrote in message
news:dmnpuf$fbj$1...@planja.arnes.si...
Saso Zagoranski
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
"Dave" <dvs...@sbcglobal.net> wrote in message
news:9k_jf.28692$Zv5....@newssvr25.news.prodigy.net...