Re: table design newbie question

Hi Todd,

> - It's immutable (for example, if you change a row's UOM, and you are
> using UOM as the primary key, all of the sudden you broke anything
> that was related to it, this is somewhat eliminated with the ON UPDATE
> CASCADE feature of SQL)

Good Point on the On Update Cascade.

> Some disadvantages of the ID primary key trick:
> - There are proprietary methods for auto numbering (code won't be
> portable or standards compliant; there are other issues with auto
> numbering, search this list for opinions)

Good Point this. However, we are not looking to port the database now.

> - It becomes ridiculous to look at (Baan is an ERP package that makes
> extensive use of integer numbers for identifying rows, what you get is
> a handful of tables with names like tccios40110 that have only two
> columns, named tfs_1 and tfs_2 respectively, that hold a bunch of
> random integers: you try to figure it out!)

Hmm. But will these dummy Serial Numbers ever be used by explicitly referring to one of the values inside the keys? I would guess that these are not designed to be used this way. But however in certain situations for instance when constructing code or while debugging one may use the values explicitly. e.g.
'where UOMID = MICMT' where MICMT stores Micro meter as description and display value.

the intended usage is
'Where SystemItems.PrimaryUOM = UnitsOfMeasure.UOMID' in Joins (Mainly Outer Joins in this case) and in lookup fetches.

>
> Personally, I prefer to use a logical uniqueness to identify data,
> like username or email, or some combination of fields. It makes my
> queries easier to write, and also easier to read.

When you put this way, I veer to your viewpoint.

I am not seeing much advantage in using UOMID as a primary key. I would prefer using UOM as Primary key with ON Update Cascade.

However, how about tables such as Purchase Order that have PO Lines as a many table. One column would be the Printed PO Number value (Printed on Purchase Orders) that may be used explicitly in queries. Should I be saving an additional autogenerated PO Number that is never displayed to the user just for the sake of having a hidden behind the scenes Column. Are there any real advantages to doing this. Why do People do it then? Is it just because its easy to autogenerate the values?

>
> My 2 cents,

Thanks. They were pretty valuable to me. I look forward to more.

> Todd

Regards,
Sanket Received on Thu May 16 2002 - 09:06:39 CEST