Board index » delphi » Opinions wanted on Master/Detail table design

Opinions wanted on Master/Detail table design

Let's say you have a database design, take a typical order entry system,
where each client has a unique account number up to 20 chars as well as
a unique integer autoinc value for each account (ie: 1,2,3, etc).  For
the detail table housing orders, would you suggest designing the detail
table using the account number or the autoinc unique account value that
means nothing to the end-user?

We have played around using both methods.  The advantages of using the
account code is that it is easy to see the account number in the detail
table without having to join the two tables.  On the other hand, the
account number is quite large and takes up a lot of space in a table
housing close to 500,000 entries.  Does anyone know based on the
internals of databases whether the database (ie: Oracle, Sybase,
Interbase) stores "a link" to the account or is the link the actual text
value of the account code?

If you opt for using the account code, what if each detail entry
required upto 5 so-called account codes?

 

Re:Opinions wanted on Master/Detail table design


Let's say you have a database design, take a typical order entry system,
where each client has a unique account number up to 20 chars as well as
a unique integer autoinc value for each account (ie: 1,2,3, etc).  For
the detail table housing orders, would you suggest designing the detail
table using the account number or the autoinc unique account value that
means nothing to the end-user?

We have played around using both methods.  The advantages of using the
account code is that it is easy to see the account number in the detail
table without having to join the two tables.  On the other hand, the
account number is quite large and takes up a lot of space in a table
housing close to 500,000 entries.  Does anyone know based on the
internals of databases whether the database (ie: Oracle, Sybase,
Interbase) stores "a link" to the account or is the link the actual text
value of the account code?

If you opt for using the account code, what if each detail entry
required upto 5 so-called account codes?

Re:Opinions wanted on Master/Detail table design


Quote
Kevin wrote in message <35CB2DE8.584CF...@zasio.com>...
>Let's say you have a database design, take a typical order entry system,
>where each client has a unique account number up to 20 chars as well as
>a unique integer autoinc value for each account (ie: 1,2,3, etc).  For
>the detail table housing orders, would you suggest designing the detail
>table using the account number or the autoinc unique account value that
>means nothing to the end-user?

Keven, you do not give much information to go on as to what you are
designing but since you said "like an Order Entry System" , I will assume
that is what you meant.  I have deployed an Order Entry system that has been
in the field for over 3 years and is being used by some 2500 companies so I
can speak from that perspective.

You say you have a Client Account number and a autoinc field.  Each order
would have a unique autoinc field number which is your only choice for
linking it to the detail table because you could potentially have more then
one order for a single client.  Therefore a client number of any kind would
be an inappropiate choice in this design.  I am not a big fan of autoinc
numbers in any case where a user may view the numbers which is certainly the
case in an Order Entry System.   If this application is to ship to many
customers and not just a single client you have to be aware that they may
want to use their own starting invoice numbers, customer, vendor  and
employee ID numbers.  In that case you will need to maintain a system table
and incrementing logic to supply these numbers instead of auto incrementing
fields.  If you are using a SQL database then it is a great use for a
trigger or stored proc.

A robust Order Entry system is quite an undertaking and there are a lot of
details to make it work properly.  Too many beginning developers think they
could just add a Master - Detail relationship, hook up to an inventory and
customer lookup table and shazam!  and OE system is born.  Nothing could be
further from the truth.  There is sales tax design, which can get quite
complicated when you have some states that have different rates as the
amount changes,  inventory adjustment when adding , or delelting a line item
quantity and provisions for tieing into an accouting system (we built our
own) but you need to at least provide the provisions to export the data.
There are a lot of other considerations to make it all come together in a
application you can really deploy.  I mention these things because it seems
, by the question you asked that you may be somewhat inexperienced.  That is
no big deal,  we all have been there but it is important to try to not bite
off more then you can handle.  If you are up to the task then by all means
go for it.  Then test the heck out of it before releasing it. Customers who
purchase these types of applications can become very hard to deal with if
you have any serious bugs.  Lawsuits are extremely common for companies who
sell this kind of software.

Good Luck!

David Farrell-Garcia
Orca Software

Re:Opinions wanted on Master/Detail table design


Wow!!!  Did you ever over analyze my original question.

The question was in regards to linking data from a detail table to a master and
the best way to link for storage purposes where mass amounts of data is
involved.  The actual application has nothing to do with an order entry system
it was just an example.  The point of the question was to address the storage
and foreign key references to the master data.  Both methods work, I was just
solicitating opinions on different methodologies.

Quote
David Farrell-Garcia wrote:
> Kevin wrote in message <35CB2DE8.584CF...@zasio.com>...
> >Let's say you have a database design, take a typical order entry system,
> >where each client has a unique account number up to 20 chars as well as
> >a unique integer autoinc value for each account (ie: 1,2,3, etc).  For
> >the detail table housing orders, would you suggest designing the detail
> >table using the account number or the autoinc unique account value that
> >means nothing to the end-user?

> Keven, you do not give much information to go on as to what you are
> designing but since you said "like an Order Entry System" , I will assume
> that is what you meant.  I have deployed an Order Entry system that has been
> in the field for over 3 years and is being used by some 2500 companies so I
> can speak from that perspective.

> You say you have a Client Account number and a autoinc field.  Each order
> would have a unique autoinc field number which is your only choice for
> linking it to the detail table because you could potentially have more then
> one order for a single client.  Therefore a client number of any kind would
> be an inappropiate choice in this design.  I am not a big fan of autoinc
> numbers in any case where a user may view the numbers which is certainly the
> case in an Order Entry System.   If this application is to ship to many
> customers and not just a single client you have to be aware that they may
> want to use their own starting invoice numbers, customer, vendor  and
> employee ID numbers.  In that case you will need to maintain a system table
> and incrementing logic to supply these numbers instead of auto incrementing
> fields.  If you are using a SQL database then it is a great use for a
> trigger or stored proc.

> A robust Order Entry system is quite an undertaking and there are a lot of
> details to make it work properly.  Too many beginning developers think they
> could just add a Master - Detail relationship, hook up to an inventory and
> customer lookup table and shazam!  and OE system is born.  Nothing could be
> further from the truth.  There is sales tax design, which can get quite
> complicated when you have some states that have different rates as the
> amount changes,  inventory adjustment when adding , or delelting a line item
> quantity and provisions for tieing into an accouting system (we built our
> own) but you need to at least provide the provisions to export the data.
> There are a lot of other considerations to make it all come together in a
> application you can really deploy.  I mention these things because it seems
> , by the question you asked that you may be somewhat inexperienced.  That is
> no big deal,  we all have been there but it is important to try to not bite
> off more then you can handle.  If you are up to the task then by all means
> go for it.  Then test the heck out of it before releasing it. Customers who
> purchase these types of applications can become very hard to deal with if
> you have any serious bugs.  Lawsuits are extremely common for companies who
> sell this kind of software.

> Good Luck!

> David Farrell-Garcia
> Orca Software

Re:Opinions wanted on Master/Detail table design


Hi

In our work we use 32 bit integers for customer codes and the like. Thus all
primary keys and foreign keys are small and fast. If for some reason you
need to have a alpha-numeric code provide one table that keeps these AND the
internal 32 bit codes. Then you can link to this table for display purposes
and use the 32 bit codes everywhere else.

Regards, Frederick C. Wilt

Re:Opinions wanted on Master/Detail table design


Kevin,

Id vote for using a meaningless ID for most tables. Once in a while there
will be changes in the Cusomer numbers -or what ever numbering you have. You
even have the possibility to leave the number blank or have it multiple
(e.g. unknown). All this is done more easily if you have the relations built
by the IDs. Deferring the PK to the FK also leads to an increasing number of
WHERE ... = ... AND ... ., so I prefer the IDs.

But be aware of the AutoIncs, the BDE does not like them. see the threads in
the NGs.

Bernd Maierhofer
dato Denkwerkzeuge
EDV-Projekte, Beratung, Softwareerstellung
Corneliusgasse 4
A-1060 Wien, Austria
Fax:  (43)(1)581 29 80
Mail: Ber...@dato.at

Re:Opinions wanted on Master/Detail table design


Use the Account number.  There are a number of good reasons for this.
The first is safety.  If you are using autoincrement numbers then when
you are doing DBA type work at your server then there is always the
possibility that your autoincrement numbers may get trashed - your DBA
could actually renumber your autoincrement field.  

Here is one scenario - Let us say that you have a master table with an
identity field as its primary key.  You identify  customers orders
with the master key.  Let us say that you have 100 customers. Only 30
of which have orders.  Customer 47 say has never ordered anything and
you or some other programmer deletes that record.  You have so far
violated no foreign keys yes?  During some database maintenance some
DBA disables constraints and re-numbers the autoincrement field.
There is the possibility that when constraints are re-enabled there
still will not be any foreign key violations.  All seems fine but any
record after No 47 that has customers is now pointing at the wrong
master record.   When the hunt for the guilty begins you want to be
living under an assumed name on a different continent.

My advice would be to generate the keyfield algorithmically from the
data contained in the master record itself before you insert the
master record.  Then use this master record identifier to identify the
customers orders.  20 characters is maybe a littel excessive for a key
field I generally use a 10 character field this gives you 36^10
different possible keys if you have a case insensitive sort order
defined - which is a lot - buy a bigger disk and more memory if you
have such worries.

All of the server databases link on the data not on some direct link
mechanism.

As for 5 account codes.  It depends what these 'account codes' are
for.  See one-to-many and many-to-many relations in any good database
design textbook.  Or give more info.....

Hope this helps

Terry Murphy

Quote
On Fri, 07 Aug 1998 10:40:09 -0600, Kevin <ksza...@zasio.com> wrote:
>Let's say you have a database design, take a typical order entry system,
>where each client has a unique account number up to 20 chars as well as
>a unique integer autoinc value for each account (ie: 1,2,3, etc).  For
>the detail table housing orders, would you suggest designing the detail
>table using the account number or the autoinc unique account value that
>means nothing to the end-user?

>We have played around using both methods.  The advantages of using the
>account code is that it is easy to see the account number in the detail
>table without having to join the two tables.  On the other hand, the
>account number is quite large and takes up a lot of space in a table
>housing close to 500,000 entries.  Does anyone know based on the
>internals of databases whether the database (ie: Oracle, Sybase,
>Interbase) stores "a link" to the account or is the link the actual text
>value of the account code?

>If you opt for using the account code, what if each detail entry
>required upto 5 so-called account codes?

Re:Opinions wanted on Master/Detail table design


I agree, I always use some meaningless value as the link in a mater-detail
relationship.  I learned my lesson a long time ago with things such as:

- Somebody changed a supplier ID, they had creted one temporarily to get a PO
out to a new supplier, but had not received the ID yet from accounting, so they
created one.  When they did receive the ID, the PO record had no matching record
in the suppliers table.

- Same client, same system.  They changed their accounting system and generated
ne codes for clients and suppliers (and other objects).  It is clear that the
system could no longer associate invoices to clients or PO's to suppliers.
That's when I implemented meaningless numeric values to achieve the link.  This
saved hundreds of hours of data entry.

- I have been using AutoIncrement values ever since, and not once have I
regretted it.  It is true that you have to look up the parent table to get a
meaningful customer ID, but it has saved me lots of maintenance nightmares.
Imagine cases where you have master-detail relationships to many levels, some
tables could be using 4 or 5 fields as to define a relationship.  I recently
inherited such a mess.

- Indexing and searching is always faster on numeric fields than text.

--
Please remove the nospam from my email address when replying to messages

Re:Opinions wanted on Master/Detail table design


Quote
Kevin wrote in message <35CB860C.C28F7...@zasio.com>...
>Wow!!!  Did you ever over analyze my original question.

>The question was in regards to linking data from a detail table to a master
and
>the best way to link for storage purposes where mass amounts of data is
>involved.  The actual application has nothing to do with an order entry
system
>it was just an example.  The point of the question was to address the
storage
>and foreign key references to the master data.  Both methods work, I was
just
>solicitating opinions on different methodologies.

Goodness then the answer is even more ambigous.  Use foriegn keys in that
scenario simplied relational integrity if it is "like" an OE sysem.   I am
surprised that was the question!  And when you say "like an Order Entry
System" you have to understand that the answer depends on what kind of
application you are talking about  as the issues change .  I therefore took
you literally.  Example:  I am building a system "like" a guideance system
to dock a space vehicle to a space station.  How should we store the data?
That would most likley elicit an entirely different response then a
different type of application.

David Farrell-Garcia

Re:Opinions wanted on Master/Detail table design


Quote
Bernd Maierhofer wrote in message <6qmvh9$q...@forums.borland.com>...
>But be aware of the AutoIncs, the BDE does not like them. see the threads
in
>the NGs.

Agreed.  autoincs do have a place in most applications but do work in
others.  I would not use them for a Customer ID or Order ID number but would
use them for a Document Reference number or a Transaction number etc.

David Farrell-Garcia
Orca Software

Other Threads