2

I'm working on putting together a new database schema. Basically we'll have a collection of companies, users, etc. All of these will have addresses/phone numbers. Instead of saving the addresses and phone numbers directly in the company or user tables we're thinking about pulling the addresses out and having one common table for address and then a linking table to link company/user to address -the reason for this is we're assuming each company or user could have multiple addresses or phone numbers.

Here is a diagram of my first attempt at a schema for this. With this one Im combining phone numbers with the address table. The pros of this schema is Im saving on extra tables for phone numbers. But in theory we could have multiple records if the address is the exact same but its a different main phone/ext/fax/alternate phone.

Diagram of first possible schema

Here is a diagram of my second attempt. Here Im thinking I could pull out phone numbers into a separate table and then use type to define home, office, cell, fax etc. I'm thinking this one could be beneficial because then if 2 users work in the same office but have different phone numbers then we would only have 1 address record and 2 phone records.

Diagram of second possible schema

I havent had a lot of luck finding any good resources about the best practices for this so any comments/suggestions would be greatly appreciated.

Also, as of right now I think most companies/users will have north american addresses or phone numbers but if I wanted to think ahead and prepare the tables for international addresses/phone numbers is there anything I should consider adding?

Kristen
  • 443
  • 1
  • 12
  • 25
  • 1
    Not an answer to your question but you may want to consider changing how you store old_password. 10 characters is way too short to store any sort of decent hashed password output. – Sam M Jan 05 '18 at 21:48
  • Use `VARCHAR(255)` as a default "string" column. Being stingy and having wacky, arbitrary lengths like 45 or 60 isn't really helping and complicates validation. Street names can be long, phone numbers can be verbose, and names, *especially names*, can be extraordinarily long. – tadman Jan 05 '18 at 21:54
  • Remember, **never** use plain-text passwords or a high-speed hash like SHA1, SHA2 or MD5. You should be using something like [Bcrypt](https://en.wikipedia.org/wiki/Bcrypt) at the absolute least. – tadman Jan 05 '18 at 21:56
  • The second approach is better because you can have metadata about the phone numbers. This follows the [Zero, One or Infinity Rule](http://en.wikipedia.org/wiki/Zero_one_infinity_rule) of [database normalization](http://en.wikipedia.org/wiki/Database_normalization) where you can now have N addresses and N phone numbers, each independent of the other. – tadman Jan 05 '18 at 22:01
  • One idea is to say that all users must belong to a least one organisation, even if that organisation is just 'Mike's House', say. – Strawberry Jan 06 '18 at 00:48
  • @kristen, can I know why do you require address table and userhasaddress table. Why cannot we have one table with AddressID(UniqueID) and CustomerID has the foreign and link it with the customer table? – Vinay Kumar May 20 '21 at 08:06

0 Answers0