CODEWITHSUNDEEP
databasedatabase-designdata-modeling
Mohamad
Mohamad

Reputation: 35349

Where do phone numbers belong in a database model?

Given a schema for a DVD rental store, should customers' phone numbers belong to the addresses table, or the users table, and why? Are there any benefits associated with one approach or the other?

Upvotes: 0

Views: 2897

Answers (7)

dave singer
dave singer

Reputation: 56

More than one customer may have the same phone number: perhaps multiple people from the same building buy from you.

What happens when you update the phone number for one customer; should it update that number for the other people that supposedly share that number?

  • Yes if the address for both parties is still the same (but that might actually break some privacy laws).
  • No if there are now two different addresses.

Upvotes: 1

Erwin Smout
Erwin Smout

Reputation: 18408

Basic cases of information modeling :

Case A. Each customer can have more than one phone number. In this case, phone number belongs in a separate table.

Case A1. It is not the case that a customer is required to have a phone number. i.e. the "relationship" is 1-1 to 0-n (i.e. assuming all phone number must always "be for" some customer). Nothing to do.

Case A2. It is the case that each customer is indeed required to have a phone number. You can model this as a relationship that is 1-1 to 1-n, but the "1" of the 1-n part is very hard to enforce in SQL systems (and in the cheapest of them, probably just impossible). That does not mean that you shouldn't be documenting the business rule properly as it is.

Case B. Each customer has AT MOST one phone number.

Case B1. Each customer is required to have a phone number. This means that each customer always has exactly one phone number. Phone number is best put in the customer table. (Note that "to have a phone number" means "to have a phone number THAT IS KNOWN TO THE STORE in question !)

Case B2. It is not required for a customer to have a phone number. In formal relational theory, it is required that you define a separate table which will hold only the known phone numbers. In informal modeling techniques such as ER and UML, you can model this as an "optional attribute". In SQL systems, many would define a nullable attribute for this.

As for "phone numbers 'belonging' to addresses" : is there any kind of "connection" between phone numbers and addresses that is relevant to your business ? I mean, let's say some customer has two addresses and two phone numbers. Is it important to know which of those two phone numbers belongs to which one of those two addresses ? What address would a cellphone number 'belong to' ?

Upvotes: 2

paxdiablo
paxdiablo

Reputation: 881563

Why do you even have an addresses table (unless you want more than one address for a given customer)?

You primary "client" is a customer. You don't rent DVDs to an address, you rent them to a person. You can't take a block of land to court when the occupant runs off with your prized "Free Willy" collectors edition trilogy.

In a world where a person only lived at one place, the address would be part of the customer table (and so would the phone in a one-phone-per-customer scenario).

If you want multiple addresses, that's fine, have a separate addresses table tying those addresses back to the customer.

But you should probably also have a similar setup for phones. Either allow up to N phone numbers per customer (with N columns) in the customers table, or (much better) have a separate phones table allowing any number of phone numbers per customer.

Something like:

customers:
    cust_id
    cust_stuff
addresses:
    cust_id references customers(cust_id)
    addr_seq_num
    addr_stuff
phones:
    cust_id references customers(cust_id)
    phon_seq_num
    phon_stuff

Upvotes: 4

Paul Keister
Paul Keister

Reputation: 13077

Contact information is notoriously difficult to model in a relational schema. In order to keep your sanity, I would advise that you make a minimum number of assuptions with respect to phone numbers. Allowing multiple phone numbers for one customer/account is good; beyond that it's hard to apply rules to phone numbers.

There is one well known exception: many pizza delivery shops use phone numbers as primary keys for customers. This works because in general there is one phone associated with the place to which one delivers pizza. On the other hand, many people no longer have land lines, so perhaps even that system is breaking down. In any case, I don't think this applies to DVD rental.

Upvotes: 1

Jim Mischel
Jim Mischel

Reputation: 134005

What do you mean by "traditional?" Since a user can have an arbitrarily large number of contact phone numbers (home, work, personal mobile, work mobile, fax, etc.), it seems like there should be a separate phone numbers table, each row of which includes a number and a value that says what type of number it is.

Upvotes: 1

jamie-wilson
jamie-wilson

Reputation: 1925

Just an assumption about your site/app, but usually I'd say "Addresses", because user information tends to be info that you pull out frequently to run the site (ID, username, visits etc) whereas phone number may not be?

Upvotes: 1

Andrew Cooper
Andrew Cooper

Reputation: 32576

There's no one correct answer to this, except to say "it depends".

It really depends on what you're modelling with your database schema. Does a phone number logically belong to a user, or an address that could potentially be shared by multiple users?

Example - a mobile phone number might be tied to a particular person, and so be part of the users table. A land-line number might be tied to a particular location or residence, and so be part of the address.

Upvotes: 2

Related Questions