Hi!

I have a table "addresses". Address can be associate to a customer, an
employee, a supplier or a consultant. Every of these 5 have a table with an
identity primary key. My question is simple: Which of the 2 ways is the best
to link the "Addresses" table to other tables?

A- By adding 5 foreign keys, assuming that only one will be NOT NULL by record
B- Adding a column Varchar "ParentTable" and a Int column "ParentID" to make
the relation programmatically?

I need the best way to be faster and also easier to implement...

Thanks in Advance!

David Parenteau wrote:
Show quoteHide quoteOr C - Create 5 tables for CustomerAddress, EmployeeAddress, etc.
Or D - Add an AddressID to the Customer, Employee, etc. tables if they
can only have one address (probably not the case)
Or E - Create a generic Person table and a PersonAddress table. Relate
Person to Customer, Employee, etc.

I don't like option A and option B is not relational.

First of all, an IDENTITY cannot ever be a key by definition, so the
schema is screwed up from the start.  Ignoring that fundamental
problem, start by asking the basic questions:

1) Is an address an attribute of a customer, an employee, a supplier
and a consultant? In which case you need a SAN (Standard Address
Number) or equivalent in a column in those tables.  I am assuming that
people fall into one and only one of these roles and that they are
logically different.  Otherwise, there would be a People table.

2) Is there a one-to-many relationship between an address and a
customer, an employee, a supplier or a consultant?  That case, you will
need tables to model the customer-addresses, the employee-addresses,
the supplier-addresses and the consultant-addresses relationships.  I
would also add type codes to the addresses (ship-to, bill-to, etc.).

About the Identity that cannot be a Key, do you mean a "Natural key"? If yes,
I'm agree.

I used to set a FieldID INT Identity has a Primary key in my tables, to
avoid multiple-field primary keys. This seems to be easier to locate a record
from a web application or application... Does this a very bad thing to do? If
yes, why?

David


Show quoteHide quote

Celko,

I now have a new example: A table activity. But the "activities" will need
to be linked to all 150 tables in my database... So, is it a reason to avoid
creating 150 link tables and keep the table name in a column of the
"Activity" table?

Thanks!!

Show quoteHide quote

Wow... cool suggestion!
Yes, more than one adress per customers, etc...

For E, does the Person table has 5 FK, one for each table or you mean 5
Person table, one for customer, one for employee, and so on?

Addresses should be the same columns for each 5 entities, so Solution C
seems to be not the quickest way to choose if a change must occur in the
schema... WHat do you think about this?

Show quoteHide quote

First create and load this table.  might want to look at Melissa Data
software to edit the addresses,

CREATE TABLE Addresses
(address_nbr INTEGER NOT NULL PRIMARY KEY,
street1 CHAR(35) NOT NULL,
street2 CHAR(35),
street3 CHAR(35),
city_name CHAR(20) NOT NULL,
state_code CHAR(2) NOT NULL,
zip4 CHAR(10) NOT NULL
   CHECK (zip4 LIKE '[0-9][0-9][0-9][0-9][0-9]'
                 OR zip4 LIKE
'[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
verification_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL);

Next, create each of the tables with the proper key for that kind of
entity.  For suppliers, the Dun & Bradsteet codes are common; employees
better have an SSN, etc.

CREATE TABLE Suppliers
(duns_nbr CHAR(9) NOT NULL PRIMARY KEY,
supplier_name CHAR(35) NOT NULL,
  ..);

Now create a relationship table between each of the entities and the
addresses:

CREATE TABLE SupplierAddresses
(duns_nbr CHAR(9) NOT NULL
   REFERENCES Suppliers(duns_nbr)
  ON UPDATE CASCADE
  ON DELETE CASCADE,
  address_nbr INTEGER NOT NULL,
  REFERENCES Addresses(address_nbr)
  ON UPDATE CASCADE
  ON DELETE CASCADE,
  supplier_address_type CHAR(5) INTEGER NOT NULL
   CHECK (supplier_address_type IN ('ship', 'bill', 'HQ', 'serv',
....)),
PRIMARY KEY (duns_nbr,  address_nbr));

Make heavy use of DRI actions to maintian data integrity.  Take time to
invent good address type codes for each logical entity (i.e. a customer
does not have a service department or a headquarters).

Thanks to all of you!

Celko, i started to forget elementary rules... I'm so guilty sometime...

David

Show quoteHide quote

Migrating from RMS III
Problems with identity column
EM can't connect to server
Problems with an INSERT SP
Date query
Save Table Definitions to Text File?
Monitoring of Replication using dev tools
SQL Query with Current date + 30 days
ORDER BY before UNION syntax error
Indexes on SQL Server