-1

Hello I need designing addresses table. I designed it but I didn't like it very much.

My needs:

  • I have customers and users (admins). I have addresses for customers and users. We need design in different tables like CustomerAddresses and UserAddresses. But we must be have Addresses table and connect with CustomerAddreses and UserAddresses.
  • I have different address types like individual, corporate address. This types just using in customer's addresses.
  • A customer can have more than one address.
  • A user can have more than one address.
  • Customers are not individual or corporate. Customer addresses are individual or corporate.

My design: (but I think this design not good. I think this design breaking normalization rules.) CustomerAddresses is junction table in this design. And belongs to CustomerAddressTypes. We can create a new design or update my design.

https://dbdiagram.io/d/5fc2218b3a78976d7b7dbaf6

enter image description here

1
  • as long as you split users and customers in two froups, there is no need for change. The databse can be quickly changed,to comfort needs. It is flexible, because the gui will make it necessary to change some things.
    – nbk
    Nov 28 '20 at 10:33
0

From my first run through on this, I would take an Object Oriented Programming eye to it. The Users and Customers tables are extremely similar, so I would probally make a single table with all the fields, and add in an additional field designating the Person as either a customer or admin. This would also remove the need for having separate join tables for addresses.

In dealing with the additional information needed for corporate addresses, I would look into adding a type field into the addresses table to denote if its a corporate customer. Then add a trigger that fires when the address type is corporate that would insert the tax information into a separate table like you already have.

Also, noticing that all three address tables contain some kind of name field, I would FK it into the Persons table, since every address would have some kind of either admin or customer already. These can be used for the corporate types as a Point of Contact name. my goal is to avoid having nearly identical tables, and avoid having the same information (like names) be recorded in multiple tables.

Addresses
    Address_ID 
    Street_Address 
    City_ID 
    Address_Type --Personal\Corporate
    Notes 
Persons
    Person_ID 
    First_Name 
    Last_Name
    Email
    Password
    Phone
    Person_Type     --Customer\Admin
Person_Address_Join
    Join_ID
    Person_ID  --FK to Persons
    Address_ID --FK to Addresses
    Notes
CorporateInfo
    Info_ID
    Address_ID  --FK to Addresses
    Tax_ID
    Tax_Office   

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.