1

I have 2 questions regarding a project. Would appreciate if I get clarifications on that.

  1. I have decomposed Address into individual entities by breaking down to the smallest unit. Bur addresses are repeated in a few tables. Like Address fields are there in the Client table as well as Employee table. Should we separate the Address into a separate table with just a linking field

    For Example

  2. Create an ADDRESS Table with the following attributes :

    • Entity_ID ( It could be a employee ID(Home Address) or a client ID(Office Address) )
    • Unit
    • Building
    • Street
    • Locality
    • City
    • State
    • Country
    • Zipcode

  3. Remove all the address fields from the Employee table and the Client table

We can obtain the address by getting the employee ID and referring the ADDRESS table for the address

Which approach is better ? Having the address fields in all tables or separate as shown above. Any thoughts on which design in better ?

Raghav Sood
  • 81,899
  • 22
  • 187
  • 195
Bhaskaran
  • 31
  • 1
  • 1
    check this out: http://stackoverflow.com/questions/6576442/should-user-and-address-be-in-separate-tables – passion Apr 20 '13 at 08:18
  • Does this answer your question? [Decision between storing lookup table id's or pure data](https://stackoverflow.com/questions/383026/decision-between-storing-lookup-table-ids-or-pure-data) – philipxy Aug 02 '20 at 05:20

2 Answers2

3

Ya definitely separating address is better Because people can have multiple addresses so it will be increasing data redundancy.

You can design the database for this problem in two ways according to me.

A. Using one table

Table name --- ADDRESS

Column Names

  1. Serial No. (unique id or primary key)
  2. Client / Employee ID
  3. Address.

B. Using Two tables

Table name --- CLIENT_ADDRESS

Column Names

  1. Serial No. (unique id or primary key)
  2. Client ID (foreign key to client table)
  3. Address.

Table name --- EMPLOYEE_ADDRESS

Column Names

  1. Serial No. (unique id or primary key)
  2. Client ID (foreign key to employee table)
  3. Address.

Definitely you can use as many number of columns instead of address like what you mentioned Unit,Building, Street e.t.c

Also there is one suggestion from my experience

Please add this five Columns in your each and every table.

  1. CREATED_BY (Who has created this row means an user of the application)
  2. CREATED_ON (At what time and date table row was created)
  3. MODIFIED_ON (Who has modified this row means an user of the application)
  4. MODIFIED_BY (At what time and date table row was modified)
  5. DELETE_FLAG (0 -- deleted and 1 -- Active)

The reason for this from point of view of most of the developers is, Your client can any time demand records of any time period. So If you are deleting in reality then it will be a serious situation for you. So every time when a application user deleted an record from gui you have to set the flag as 0 instead of practically deleting it. The default value is 1 which means the row is still active.

At time of retrieval you can select with where condition like this

select * from EMPOLOYEE_TABLE where DELETE_FLAG = 1;

Note : This is an suggestion from my experience. I am not at all enforcing you to adopt this. So please add it according to your requirement.

ALSO tables which don't have any significant purpose doesn't need this.

Nikhil Agrawal
  • 26,128
  • 21
  • 90
  • 126
1

Separating address into a seperate table is a better design decision as it means any db-side validation logic etc. only needs to be maintained in one place.

Glitch Desire
  • 14,632
  • 7
  • 43
  • 55