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 reco
rd
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:
> 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!
Or 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.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||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.).|||Wow...
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?
"David Gugick" wrote:
> David Parenteau wrote:
> Or 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.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||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).|||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 recor
d
from a web application or application... Does this a very bad thing to do? I
f
yes, why?
David
"--CELKO--" wrote:
> 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.).
>|||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!!
"--CELKO--" wrote:
> 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.).
>
