Geek Answers Handbook

Plain one-to-many table for multiple objects

Suppose I have two tables: Customer and Supplier. I want to have a common address table for the addresses of customers and suppliers. Customers and suppliers can have one to many addresses.

Option 1

Add columns for AddressID to tables Customerand Vendor. It just doesn't seem like a clean solution for me.

Customer     Vendor         Address
--------     ---------      ---------
CustomerID   VendorID       AddressID
AddressID1   AddressID1     Street
AddressID2   AddressID2     City...

Option 2

Move the foreign key to the table Address. For the customer will be filled out Address.CustomerID. For the seller will be filled Address.VendorID. I don’t like it either - I don’t need to change the address table every time I want to use it for another object.

Customer     Vendor         Address
--------     ---------      ---------
CustomerID   VendorID       AddressID
                            CustomerID
                            VendorID

Option 3

- 1 Address , , . , . , .

Customer     Vendor         Address     
--------     ---------      ---------
CustomerID   VendorID       AddressID
                            FKTable
                            FKID

, , , ?

+5
4

, - "", ; "". "" - . , , . , "":

Organizations       Vendors               Customers
--------------      ---------------------  ---------------------
OrganizationID(PK)  OrganizationID(FK/PK)  OrganizationID(FK/PK)
AddressID1(FK)
AddressID2(FK)

"" , "" , "" . "", "" "" , .

+7

, , , 1. , , , . 2, , , , . , , , ID , , , , CustomerID VendorID. 3, FKID FK. , , FK . , ORM , , , "" , "".

, " " , .

Customer
--------
CustomerID (PK)

Vendor
------
VendorID (PK)

Address
-------
AddressID (PK)

CustomerAddress
---------------
CustomerID (FK/PK)
AddressID (FK/PK)

VendorAddress
-------------
VendorID (FK/PK)
AddressID (FK/PK)
+6

, ? ,

Customer
    customerId (PK)
    addressBookId (FK to AddressBook)

Vendor
    vendorId (PK)
    addressBokId (FK to AddressBook)

AddressBook
    addressBookId (PK)

Address
    addressId (PK)
    addressBookId (FK to AddressBook)
+1

, :

 Address_Type  (a flag to say that this is a customer, or a vendor)
 ID            (a common ID for both customer and vendor and depending on the flag you know what ID it is)
 Address       (Data of address itself)

:

  • , .
    • ()
  • , :
    • object identifier (FK for the object identifier in the above table)
    • type of object (customer or supplier)
0
source

More articles:


All Articles