1

I have a database with contacts in it. There are two different types of contacts, Vendors and Clients.

The Vendor table has a vendor_contacts table attached via foreign key value to allow for a one to many relationship. The client has a similar table.

These contacts can have a one or many relationship with a phone numbers table. Should i have a separate phone numbers table for each of these or one shared phone number table with two foreign keys allowing one to be null?

OPTION 1

enter image description here

Here I would have to enforce that one of vendor_id or client_id was NULL and the other not NULL in the shared phone table.

OPTION 2

enter image description here

Here each table would have its own phone number table.

Improve this question
5
  • I'm not sure I understand the question - can you please post the current schema, and the two options you're considering? Commented May 26, 2017 at 14:28
  • "The Vendor table has a vendor_contacts table" I'm assuming one of those "table" is a typo. You don't have a table inside a table, do you? Commented May 26, 2017 at 14:33
  • @NevilleK edited my question. Hopefully thats easier to understand. :) Commented May 26, 2017 at 14:38
  • @SiggiSv No, I meant that they are connected via foreign key. I changed that description a bit. Commented May 26, 2017 at 14:39
  • I see a pattern -- 5 answers, all saying to combine clients and vendors into a single table! Unanimous? Commented May 26, 2017 at 19:56

5 Answers 5

Reset to default
4

TBH I would merge the vendor and client tables and have a 'contact' table. This could have a contact type and would allow for newer contacts to be added. Consider you want to add something to your contacts - address, you may have to change each table in the same way, then you want birthday (OK maybe not but just as an example) and again, changes to multiple tables. Whereas if you have a single table, it can reduce the overhead of managing this. This will also mean you have one contact phone number table!

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

"wasting space" is not really a meaningful concern in modern database systems - and "null" values are usually optimized by the storage engine to take no space anyway.

Instead, I think you need to look at likely query scenarios, at maintainability, and at intelligibility of your schema.

So, in general, a schema that repeats itself - many tables with similar columns - suggest poor maintainability, and often lead to complicated queries.

In your example, imagine a query to find out who called from a given number, and whom they might have been trying to reach.

In option 1, you query the phone number, and outer join it to the two contact tables - relatively easy. In option 2, you have a union of two similar queries (only the table names would change) - duplication and lots of chance for bugs.

Imagine you want to break the phone number into country, region and phone number - in option 2, you have to do this twice (and modify all the queries twice); in option 1, you have to do this only once.

In general terms, repetition is a sign of a bad software design; this also counts for database schemas.

That's also a reason (as @siggisv and @NigelRen suggested) to flatten the vendor_contact and client_contact tables into a single table with a "contact_type" column.

Improve this answer

Comments

3

I would use two different tables, a vendor_contacts table and a client_contacts table. If you only have one table, you always waste space as you will have in each row a null column

Improve this answer

Comments

2

option 2

but change vendor_contact and client_contact to 'contact'

and add a 'type' column to 'contact' that identified 'Client' or 'vendor' if you need to separate the records.

Improve this answer

4 Comments

I was thinking of this too, however another answer mentions that there would be wasted space for null entries and in the long run wouldn't this be detrimental?
there should be no null entries here.. one row for every client and one for every vendor - and as many phone numbers as you have only..
But every entry in phone would have either vendor_id = Null or client_id= Null
nope - the phone FK would point to the 'client_id' - only one of those columns.. so no nulls.
2

I would do as others have suggested and merge vendor_contact and client_contact into one contact table.

But on top of that, I doubt that contact<->phone is a one-to-many relationship. If you consider this example you will see that it's a many-to-many relationship:

"Joe and Mary are both vendors, working in the same office. Therefore they both have the same landline number. They also have each their own mobile number."

So in my opinion you would need to add a contact_number table with two columns of foreign keys, contact_id and phone_id.

Improve this answer

1 Comment

The overhead of many to many just because two people may use the same number isn't worth it. You then have to work out when you remove one of the parties to see if it's the last reference - why bother for a couple of extra records?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.