I have a client with a table structure in the column order shown below:

Unit_ID
Process_Cell_ID
Area_ID
Site_ID
Enterprise_ID

These are all keyed fields and integer type.

I am perplexed by the order of the table columns. I can't think of any
advantage to ordering the table columns as shown. It seems to me to be
better to show the reverse.

I have always structured tables with the largest to smallest as the key
fields. Any comments on what advantage there is in ordering a table
structure from smallest to largest and using integers as the field type in
lieu of alphanumeric, would be appreciated.

Thanks
Dan

Dan wrote:

That PK as defined is probably next to useless for searching, since its
leading edge is probably just a meaningless key imposed for uniqueness. I
assume there are secondary indexes on each of those columns as well, plus
some indexes with concatenated indexes.

Both integers and alphas are safe Paradox keys. Integers are marginally
easier to increment in a next number scheme.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.

Field order is not generally all that important with the exception that the PK
must be first and new columns are best added at the end of the roster rather
than in the midst of existing fields.

As to that Key: YUCK! EWWW......

Generally a single meaningless integer key is best. Concatenated keys are more
fragile and, since they are carried by all the secondary indices, make the
secondaries larger and, probably, less efficient. If you need to define
relationships among a bunch or other key fields (which is what the table you
show looks like) it would be best to have a meaningless integer key on the
table, possibly a unique secondary on the whole set of fields you showed to
enforce uniqueness in the ay a KV would in your current table and then
secondaries as needed for other uses.

Denn Santoro
President
Resource Development Associates
http://www.RDAWorldWide.Com
Offices in the United States and Germany
Providing solutions to health care, business, governments and non-profits
since 1982

Is this table a master in RI?

Yoy may have some detail tables keyed on Unit_ID, Process_Cell_ID and so
on...

"Dan" <megaflex (AT) bellsouth (DOT) net> wrote

I have further clarification on the schema for the table structures. There
is one additional field that is also keyed "Name". I found out that the use
of an interger number to link all tables so that the user data values(Name)
can always be changed without effecting the relationship between tables. An
autoincrement number generator is used to assign the next available number
to the key field. Using this methods allows the "Name" to be changed without
affecting the relationship to the other table relationships.

I have not used this method before, but it sounds feasible. Has anyone else
used this schema before? It seems to me that reversing the table structures
name field at the bottom would work as well. In addition, using an alpha key
field value would be just as viable as an autoincrement key field. Are there
any real advantages here that I am missing?

Thanks for you comments

Unit table

*Unit_ID
*Process_Cell_ID
*Area_ID
*Site_ID
*Enterprise_ID
*Name

Process Cell table

*Process_Cell_ID
*Area_ID
*Site_ID
*Enterprise_ID
*Name

Area Table

*Area_ID
*Site_ID
*Enterprise_ID
*Name

The structure looks VERY strange to me but without knowing what the app is
doing it's difficult to give advice.

As to using the autoincrement function of Paradox - search this NG and you
will loads of posts, most of them recommend against using it.


Anders

What a lot of people do is to have a single field PK based on a next number
scheme (usually not a Paradox autoincrement field, but an actual next number
generator) with indexes on the other fields, which are used as foreign keys.
With Paradox, this results in a much smaller PK and secondary indexes, and
possibly better performance as a result.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.

The table structure shown is managing device data in a manufacturing plant
at the lowest(unit) level. I am merely requesting comments for the overall
data structures advantages or perhaps a better structure. I am using Paradox
as the front end and MSSQL as the database.

"Anders Jonsson" <gt3TakeThisAway (AT) bredband (DOT) net> wrote

It seems to me that if I use numbers as PK values this would limit my
ability to export the data to other users that have the same software and
wish to merge the data together. It may conflict with another database
numbering scheme. I don't know if that would be an advantage over an apha
numeric field, however. At least in the numeric PK I wouldn't have to come
up with a value to enter.

Dan


"Larry DiGiovanni" <nospam (AT) nospam (DOT) com> wrote

I don't know why. Rarely does one export data from one database and
import it into another without massaging it along the way.

On the other hand, you say "same software" - if by that you mean the
same application, then I don't know how you could be modifying the
data structure without breaking the application...

Note that I haven't read this thread in detail and my brain has been
fried, so feel free to ignore this post.

Liz


Dan wrote: