Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2014
    Posts
    1

    Question table design issue for normalizing multiple catogories sharing similar data

    I'm little confused in a table design.

    Method 1) With first method i can select all phone number from phone_book table and filter out employee phone numbers by specifying type.

    Method 2) In second method i can select all by joining two table and can retrieve employee phone numbers.

    Employee and customers can have multiple phone numbers. And same with the address details.

    I've same issue with addresses. I've address of employees, customers, staffs and others. I've googled and still cant choose which is right method. Which is the right method and why the other not and any other better design for this ?

    Method 1
    Code:
    employee
    id | name 
    1      e
    
    
    customer
    id | name
    1     c
    
    
    phone_book
    type | fk_id |     phone
      e      1         123123123
      c      1         451323123
    Method 2
    Code:
    employee
    id | name 
    1      e
    
    
    customer
    id | name
    1     c
    
    
    employee_phone_book
    emp_id  |     phone
       1         1231233434
       2         6273343423
    
    
    customer_phone_book
    cus_id  |  phone
       1       5231233434
       2       1251233434

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    I would prefer putting them together if it were me.

    Code:
    People
    id | ent_typ | name 
    1  |   emp    |  e
    2  |   cust   |  c
    
    phone_book  
    fk_id | phone_seq  |  phone
      1   1      123123123
      1   2      123123124
      1   3      123123125
      2    1     451323123
    
    address_book
    fk_id | addr_seq  | addr_line_1 .....
    1  1  123 his st. .....
    1  2  124 his st. .....
    1  3  125 his st. .....
    2    456 her ave. ....
    With that method you can still get address or phones for just employees or customers with a simple join to the person table. On the phone and address tables, you probably want a type as well, eg: mobile, home, office, etc... Address for primary, vacation, office space 1, office space 2, etc...
    Dave

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •