Database Design Questions -

i have 2 questions regarding project. appreciate if clarifications on that.

1. i have decomposed address individual entities breaking down smallest unit. bur addresses repeated in few tables. address fields there in client table employee table. should separate address separate table linking field

   for example

2. create address table following attributes :

   • entity_id ( employee id(home address) or client id(office address) )
   • unit
   • building
   • street
   • locality
   • city
   • state
   • country
   • zipcode

3. remove address fields employee table , client table

we can obtain address getting employee id , referring address table address

which approach better ? having address fields in tables or separate shown above. thoughts on design in better ?

ya separating address better because people can have multiple addresses increasing data redundancy.

you can design database problem in 2 ways according me.

a. using 1 table

table name --- address

column names

1. serial no. (unique id or primary key)
2. client / employee id
3. address.

b. using 2 tables

table name --- client_address

column names

1. serial no. (unique id or primary key)
2. client id (foreign key client table)
3. address.

table name --- employee_address

column names

1. serial no. (unique id or primary key)
2. client id (foreign key employee table)
3. address.

definitely can use many number of columns instead of address mentioned unit,building, street e.t.c

also there 1 suggestion experience

please add 5 columns in each , every table.

1. created_by (who has created row means user of application)
2. created_on (at time , date table row created)
3. modified_on (who has modified row means user of application)
4. modified_by (at time , date table row modified)
5. delete_flag (0 -- deleted , 1 -- active)

the reason point of view of of developers is, client can time demand records of time period. if deleting in reality serious situation you. every time when application user deleted record gui have set flag 0 instead of practically deleting it. default value 1 means row still active.

at time of retrieval can select condition this

select * empoloyee_table delete_flag = 1; 

note : suggestion experience. not @ enforcing adopt this. please add according requirement.

also tables don't have significant purpose doesn't need this.