[quoted text, click to view]

First post some specs, sample data and DDL. Then get a real, verifiable
key. By definition, the auto-increment cannot be a key. In fact, the
non-DDL that you posted looks wrong -- why is a product_id and a product
different?

[quoted text, click to view]
have data in each of these tables (which means some times Table1-5ID are
null). <<

Unh? Again, by definition, an identifier (key) cannot be a NULL. You
sound like you are trying to build some kind of network database in SQL
and want to use NULLs as nil pointers in a linked list.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Hi,

I have a question about database design. I have a main table that has 5
child tables referring to it through a one to one relationship. The design
of my table is the following:

Products
ProductID (autoincremental)
ProductCode
....
Table1ID (foreign key)
Table2ID (foreign key)
Table3ID (foreign key)
Table4ID (foreign key)
Table5ID (foreign key)

Each table's ID is autoincremental and each product does not have to have
data in each of these tables (which means some times Table1-5ID are null).
This is design is difficult to insert and update data via a client
application. For instance, when inserting data I have to insert Table1-5
data before the Products table and it makes updating very complex (updating
via the client is already complex because update data is somtimes pulled
from another source and the client does not if record exists....) I thought
about redesigning the products and table1-5 to this:

Products
ProductID
ProductCode...

Tables1-5
ProductID (foreign key)

Use the productID to be the key in tables1-5 which would make it easier to
work with. Is there a better way to do this? Can I get away with using the
productID as the key for tables1-5? Any suggestions would be appreciated.

Thanks

I agree with Alan, the foreign key reference should point from the child
table back to the "primary" table (e.g. the one containing the actual entity
you are modeling, in this case a "product")... this also makes it much
easier to add table6, table7, table8, etc.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


[quoted text, click to view]

Thanks for the replies. I have one more question regarding the design. The
reason I went with the design having the keys for table1-5 in the products
table made it easy for me to check and see if there was data for that
product in the tables1-5. What is the best way to mimic this? I would like
on the client side be able to view the products table and see if there other
data in the other tables I need to retrieve.

Thanks

[quoted text, click to view]

Alan,

Thanks for the reply. The product I am representing in the my table design
is highly comfigurable with many options. These 5 tables are the different
options that the product can have and the tables are organized into 5
logical tables. I could have one products table however there would be alot
of nulls for each product. So, I decided to normalize the table and it came
out to be 5 tables and one main products table. The products table
represent the main product, what I mean by this is the product will always
have data for the fields in the products table. Each table has a one to many
relationship with the products table. I think you might be right that I
could use a summary table that would keep track of the options tables. What
do you think?

Here is an example of the DDL for some of the tables:

CREATE TABLE [dbo].[Products] (
[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
[MCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Generic Options] (
[GenericOptionID] [int] IDENTITY (100, 1) NOT NULL ,
[ProductID] [int] NOT NULL ,
[VCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ModelOptions] (
[ModelOptionID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductID] [int] NOT NULL ,
[SR] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SD] [int] NULL ,
[Jac] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LSize] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PIn] [int] NULL ,
[ILength] [float] NULL ,
[ILengthUnit] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SHole] [int] NULL
) ON [PRIMARY]
GO

Thanks

[quoted text, click to view]

[quoted text, click to view]

And what happens when you have to insert more than one child record in any
one of your child tables?? You'll be trying to insert duplicate values for
your primary key.

Consider:

Products
-ProductID (Integer, Identity, Primary Key)
-ProductCode (...)

Tables1-5
-Table1ID (Integer, Identity, Primary Key)
-ProductID (Foreign Key)

The PK of each child table is an Identity column (or a suitable key selected
from the candidate columns), and each child table contains a column
containing the foreign key reference to the appropriate record in the
Products table, i.e. the ProductID.

Alan

[quoted text, click to view]

The regulars are cringing now :)

A table is a set of things or relationships of the same kind. A key is
a subset of attributes in the entities being modeled which uniquely
identify each element of the set of entities. It is part of

An auto-numbering is based on a PHYSICAL location or state inside a
machine. It has absolutely nothing to do with the data model or the
reality of the data.

A key should be verifiable within itself. That means that when I see a
particular kind of identifier, I ought to know if it is syntactically
correct. For example, I know that ISBN 0-486-60028-9 has the correct
number of digits and that the check digit is correct for a proper
International Standard Book Number. Later on I can find out that it
identifies the Dover Books edition of AN INVESTIGATION OF THE LAWS OF
THOUGHT by George Boole.

An identifier should have repeatable verification against the reality
that you are trying to capture in your data model. If I put the same
data into another databse, do I get the same auto-increment number?
Nope!

Exactly what verification means can be a bit fuzzy. At one extreme,
prison inmates are moved by taking their fingerprints at control points
and courts want DNA evidence for convictions. At the other end of the
spectrum, retail stores will accept your check on the assumption that
you look like your driver's license photograph.

What you are doing is faking a network database using IDENTITY for
pointers, instead of creating an RDBMS.

Let me go ahead one more step and play Q&A with the direction I think
you are going:

Q: Couldn't a compound key become very long?

A1: So what? This is the 2000's century and we have much better
computers than we did in the 1950's when key size was a real physical
issue. What is funny to me is the number of idiots who replace a
natural two or three integer compound key with a huge GUID that no human
being or other system can possibly understand because they think it will
be faster and easy to program.

A2: This is an implementation problem that the SQL engine can handle.
For example, Teradata is an SQL designed for VLDB apps that uses hashing
instead of B-tree or other indexes. They guarantee that no search
requires more than two probes, no matter how large the database. A tree
index requires more and more probes as the size of the database
increases.

A3: A long key is not always a bad thing fro performance. For example,
if I use (city, state) as my key, I get a free index on just (city). I
can also add extra columns to the key to make it a super-key when such a
super-key gives me a covering index (i.e. an index which contains all of
the columns required for a query, so that the base table does not have
to be accessed at all).

[quoted text, click to view]
acceptable (in the relational model) to have an Identity attribute to
use as a handle to the row, and for attributes in other tables to use as
the target for a foreign key? <<

A handle to the row? Oh, you mean faking a sequential file's positional
record number, so I can reference the physical storage location? Sure,
if I want to lose all the advantages of an abstract data model, SQL set
oriented programming, carry extra data and destroy the portability of
code!

More and more programmers who have absolutely no database training are
being told to design a database. They are using GUIDs, IDENTITY, ROWID
and other proprietary auto-numbering "features" in SQL products to
imitate either a record number (sequential file system mindset) or OID
(OO mindset) since they don't know anything else.

Experienced database designers tend toward intelligent keys they find in
industry standard codes, such as UPC, VIN, GTIN, ISBN, etc. They know
that they need to verify the data against the reality they are modeling.
A trusted external source is a good thing to have.

The IDENTITY column is a holdover from the early programming languages
which were very close to the hardware. For example, the fields (not
columns; big difference) in a COBOL or FORTRAN program were assumed to
be physically located in main storage in the order they were declared in
the program. The languages have constructs using that model -- logical
and physical implementations are practically one! The data has meaning
BECAUSE of the program reading it (i.e. the same bits could be a
character in one program and be an integer in another).

The early SQLs were based on existing file systems. The data was kept
in physically contiguous disk pages, in physically contiguous rows, made
up of physically contiguous columns. In short, just like a deck of
punch cards or a magnetic tape. Most programmer still carry that mental
model, which is why I keep doing that rant about file vs. table, row vs.
record and column vs. field.

But physically contiguous storage is only one way of building a
relational database and it is not the best one. The basic idea of a
relational database is that user is not supposed to know *how* or
*where* things are stored at all, much less write code that depends on
the particular physical representation in a particular release of a
particular product on particular hardware at a particular time.

One of the biggest errors is the IDENTITY column (actually property, not
a column at all) in the Sybase/SQL Server family. People actually
program with this "feature" and even use it as the primary key for the
table! Now, let's go into painful details as to why this thing is bad.

The first practical consideration is that IDENTITY is proprietary and
non-portable, so you know that you will have maintenance problems when
you change releases or port your system to other products. Newbies
actually think they will never port code! Perhaps they only work for
companies that are failing and will be gone. Perhaps their code is such
crap nobody else want their application.

But let's look at the logical problems. First try to create a table
with two columns and try to make them both IDENTITY. If you cannot
declare more than one column to be of a certain data type, then that
thing is not a datatype at all, by definition. It is a property which
belongs to the PHYSICAL table, not the LOGICAL data in the table.

Next, create a table with one column and make it an IDENTITY. Now try
to insert, update and delete different numbers from it. If you cannot
insert, update and delete rows from a table, then it is not a table by
definition.

Finally create a simple table with one IDENTITY and a few other columns.
Use a few statements like

INSERT INTO Foobar (a, b, c) VALUES ('a1', 'b1', 'c1');
INSERT INTO Foobar (a, b, c) VALUES ('a2', 'b2', 'c2');
INSERT INTO Foobar (a, b, c) VALUES ('a3', 'b3', 'c3');

To test the tables individually you could join from Products to each of your
child tables on the ProductID, or use a subselect to retrieve, say, the
count of products with that ProductID in a particular child table. Perhaps
another question is why you need five different child tables (we don't
really have enough information) - assuming you do, maybe you need another
summary table that keeps track of the Products and where the related records
reside??



[quoted text, click to view]

[quoted text, click to view]

Hi Joe,

I'm interested in what you're implying here - care to elaborate?

Alan

[quoted text, click to view]

I was waiting for that response. :) I understand everything you're
saying, and in *theory* it all sounds great. However, all of us "idiots"
work in the real world and are forced to make design decisions that
might not fit the nice, neat, complete scenarios you come up with.

For example, what would you make the primary key be for a table of
people in a database is international? SSN won't work since not only
does not every US citizen have one, but no one outside the US has one
not to mention that you can't always get people's SSN for security
reasons. Name obviously won't work. Height? Weight? Oh, I know, why not
require your company to install fingerprint machines in every location
and require ever person that you plan on putting into the database come
into the office and submit their fingerprint? After all, I'm sure when
you simply explain to your boss that this is necessary to have a
"properly" modeled database he'll be more than happy to foot the bill!
The truth is, in the real world, we don't always have keys that are
inherent in the data and having some sort of system for a generic key is
essential. I'm not saying that you should, by default, throw an identity
column in every table with a field called "ID", but I am saying that
sometimes you have to have some sort of key that is not built from the
data. With your vast experience in the industry, I honestly can't
imagine that you've not encountered this situation multiple times. I
know I have.

The simple fact is that in the real world we quite often do not have
complete data to work with. It is not uncommon to have situations where,
for example, you may only get a name and address for someone until they
are a full employee or something like that. Once that has happened THEN
you can get their SSN. In this case, should we have the person’s first
name, last name, middle name, address1, address2, city, state and zip
make up the primary key? Is that *really* preferable to having a generic
numeric key? I know you're quick to claim that when people use the
identity column that they’re trying to go back to the tape days in the
50's and duplicate that. Well, I can tell you that I'm not. I wasn't
alive in the 50's. When I do use an identity column as a primary key, it
is because I can't be guaranteed to have a reliable, complete primary
key within the data. I could care less *what* the identity column comes
up with for the key. I don't care if it is sequential. I don't care if
there are gaps; all I care about is that it is unique.

Alan,

I appreciate your response. You might have misunderstood me or I
misunderstood your response. The child tables represent product options and
not product typed. Since this is a highly comfigurable product (millions of
different computations) I do not use the products table like the Northwind
Example where the products are already in the products table. I rather save
the product from the client (user input) into the database. Each product is
potentially different and with that in mind these child tables are used or
not used to hold data (it all depends on the user input). Why do you
suggest having both a foreign key the products table and productID in the
child tables?

Thanks
[quoted text, click to view]