Re: table design newbie question

sanket_at_wcommunities.com (sanket) wrote in message news:<91bce909.0205102239.60e41401_at_posting.google.com>...
> Table UnitsofMeasure as it currently looks:
> UOM (Text) : The name of of UOM
> UOMDescription (Text) : Short Description for the UOM
>
> What are the advantages of:
> 1. Adding a dummy serial number Primary Key in the Units of Measure Table
> 2. Alternatively, making the Field 'UOM' as Primary Key
> 3. Alternatively, Changing the design to
> UOMID (Text) : A 5 Character Unique ID
> UOMShortDescription (Text) : The displayed UOM
> UOMLongdescription (Text) : Available only in certain reports
>

This seems to be a continued source of debate in this newsgroup.

> What are the issues that should have bearing on this decision:
> Number of rows in table?
> Maintainability of Code?
> Others?

You will tend to find people that feel strongly one way or another.

Some advantages of the ID primary key trick: - A lot of designers use it and are familiar with it - There are proprietary methods for auto numbering these fields (an easy guarantee on uniqueness)
- 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)
- It will take up less space and/or have better performance (you only have integers in your related tables, not the spelled out UOM; this is less of a problem these days with the disk space available).

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) - Most of the time, it doesn't have real-world equivalence (there are exceptions like order number sequences, etc.) - 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!)

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.

My 2 cents,
Todd Received on Tue May 14 2002 - 02:17:21 CEST