Hi All,
I need to decide on the DB model. Here the address entity is common for different entities like CUSTOMER, BUSINESS, EMPLOYEE etc. One straight forward way if Option 1 below. This will enforce DB integrity. However I feel this is not very smart approach because, too many redundant tables, not very easily extendible, and increased coding on application side.
Option 2, below removes one layer completely, but foreign key constarint can not be enforced, because the ID may be from any of the mentioned tables. However we can enforce DB integrity using trigger. This simplifies DB modeling, application code and is extendible, If in future one more entiy is added which requires address, simply change the trigger, with very minimum effort on application layer, the address entity could be reused.
Option 3 could be (I have not put the structure below) only one common link table for all 3 tables. This will help to use the same address for different entities. But this is not one of the requirement for me now.
I would like hear from experts, pros and cons, has any one done this before and any advice you may want to give.
Code:
Option 1:
CUSTOMER
|-----------------|1
|CUSTOMER_ID(PK) |----| CUST_ADDR_LINK
|CUSTOMER_NAME | |M |-----------------|
|............... | |->|CUSTOMER_ID (FK) |1
|-----------------| |ADDRESS_ID |---|
|-----------------| |
|
BUSSINESS |
|-----------------|1 |
|BUSINESS_ID(PK) |----| BUS_ADDR_LINK | ADDRESS
|BUSINESS_NAME | |M |-----------------| | 1|---------------|
|............... | |->|BUSINESS_ID (FK) |1 --------|ADDRESS_ID(PK) |
|-----------------| |ADDRESS_ID |---| |HOUSE_NO |
|-----------------| | |STREET_NAME |
| |CITY .... |
EMPLOYEE | |---------------|
|-----------------|1 |
|EMPLOYEE_ID(PK) |----| EMP_ADDR_LINK |
|EMPLOYEE_NAME | |M |-----------------| |
|............... | |->|EMPLOYEE_ID (FK) |1 |
|-----------------| |ADDRESS_ID |---|
|-----------------|
Option 2:
CUSTOMER
|-----------------|1
|CUSTOMER_ID(PK) |----|
|CUSTOMER_NAME | |
|............... | |
|-----------------| |
|
|
BUSSINESS |
|-----------------|1 |
|BUSINESS_ID(PK) |----| ADDRESS
|BUSINESS_NAME | | 1|---------------|
|............... | | |ADDRESS_ID(PK) |
|-----------------| |------>|ID |
| |ENTITY_TYPE |<== This will have CUSTOMER
| |HOUSE_NO | ,BUSINESS,EMP etc.
| |STREET_NAME | that decides relation for
EMPLOYEE | |CITY .... | the ID field.
|-----------------|1 | |---------------|
|EMPLOYEE_ID(PK) |----|
|EMPLOYEE_NAME |
|............... |
|-----------------|
Login
FAQ
Search