SQL Server - table design

Asked By morphius

20-Nov-09 07:07 AM

Based on the table info below I need some suggestion(whatever is needed e.g
datatypes) to design this wherein the work order will always be unique. The
challenge is that there are multiple standalone systems in the field with the
same databases used by technicians. If the work order number is inserted
between standalone systems, I want to be able to uniquely identify the same
work order number even if it is on separate standalone system(laptop).

work_order table
(
work_order_number_id
work_order_name
company_name_id
technician_id
date_completed
)

work_breakdown table
(
work_breakdown_id
work_breakdown_name
work_description
work_order_number_id
)

20-Nov-09 08:37 AM

One method is to add another column (e.g. site_id) and use the composite of
site_id and work_order_number_id as the primary key.

Another approach is to use a uniqueidentifier data type for
work_order_number_id so that you can use GUID values as the surrogate
primary key.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

20-Nov-09 09:19 AM

IMHO GUIDS suck if people ever have to use them, how do you like entering
the Microsoft key when you do an install?

You just need a two-part key

work_order_origin       char(5),
work_order_number    identity (1,1)

20-Nov-09 09:50 AM

I am leaning towards using composite keys. Would this yield better
performance compared to uniqueidentifier?

20-Nov-09 11:44 AM

How many rows are you talking about? If it is under 100,000 per year and
you are using a reasonable PK, performance of the composite key should not be
an issue.

20-Nov-09 11:13 PM

If the work_order_number is incremental, I would expect the composite key
approach to perform better than a single random GUID.  You'll get less
fragmentation and better locality of reference.  It will be a closer call if
you assign uniqueidentifier values using NEWSEQUENTIALID.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/