1

I'm not a DBA and I don't know what is the best solution. I have two tables, 

Custumers Table

CustomerId (primary key, identity)
...

and 

Suppliers Table

SupplierId (primary key, identity)
...

and I want to store multiple telephone number and multiple emails. I thought to create two other tables, Emails and Telephones and use those in join with my Custumers and Suppliers, something like

Telephones Table

Id
UserId (reference to SuppliersId or CustomerId)
Value
...

But if I use as key for custumers and suppliers an Identity I'll have for sure problems. I'm thinking to do something like

Telephones Table

Id
SuppliersId
CustumersId
Value
...

But I don't know if is a good design. Any advice? Thank you

David Brabant
  • 41,623
  • 16
  • 83
  • 111
Davide
  • 1,305
  • 3
  • 16
  • 36

5 Answers5

3

A good design would be

  • Customers: Table of customers - CustomerId, Other columns
  • Suppliers: Table of suppliers - SupplierId, Other columns
  • Telephones: Table of telephones - TelephoneId, other columns
  • CustomerTelephones: CustomerId, TelephoneId
  • SupplierTelephones: SupplierId, TelephoneId
Vikdor
  • 23,934
  • 10
  • 61
  • 84
  • 1
    +1 - but only if you want a many-to-many relation between customers/suppliers and telephone numbers. – tomfanning Jan 22 '13 at 09:28
  • Right @tomfanning. BTW that was OP's requirement to store multiple telephones and emails. If it was one-to-one, then the Customer and Supplier tables can simply contain a reference to the telephone ID. – Vikdor Jan 22 '13 at 09:30
  • Actually I read OP's requirement as multiple phone numbers per customer, and multiple phone numbers per supplier. This would best be solved with a CustomerTelephones and a SupplierTelephones table, each with a foreign key back to the relevant table. This supports referential integrity. If all telephone numbers need to be queried in the same table, I'd just overlay the two tables with a view. – tomfanning Jan 22 '13 at 09:40
  • Thanks. this seems work for me, also @dukeling solution is interesting .. I'm not expert with views and I don't really know their advantages .. I'm going to study a bit :) – Davide Jan 22 '13 at 09:54
  • 1
    This is a solution, a good one even, but not an ideal one. It does help address (ho ho!) one thing which the OP apparently hasn't considered: when suppliers are also customers. Not every business has this scenario but it does happen. – APC Jan 22 '13 at 10:21
  • Thanks for your comment @APC, just interested to know what could be done to make this an ideal solution... – Vikdor Jan 22 '13 at 12:05
  • In this context the definition of "ideal" would be "perfect fit with requirements". In my opinion the requirements (as presented) are vague or incomplete, and so I don't think we can say *any* solution is ideal. – APC Jan 22 '13 at 12:32
  • @APC, got it, I updated the language in my answer accordingly. – Vikdor Jan 22 '13 at 12:33
  • For instance, if we wanted to add a new table - say THIRD_PARTIES - or attach some additonal informaton to the intersection tables - say CONTACT_TYPE - your approach would generate more work than the SUuper-Type/Sub-Type deisgn proposed by Dukeling. – APC Jan 22 '13 at 12:41
  • Nope, not a good design. A customer can also be a supplier in the real world. – Neil McGuigan Jan 22 '13 at 18:39
  • 1
    @NeilMcGuigan, A given person can be a supplier and customer but the attributes of a customer and that of a supplier will be different fundamentally and hence you would still have a record each of those tables. But the telephones table would have a single record and the same telephoneId can be put in both CustomerTelephones and SupplierTelephones. Would you mind elaborating on why this model is bad in such cases? – Vikdor Jan 23 '13 at 04:35
  • A person is a person and should be in a (legal) person table. A customer is role that a person plays over time. If your schema accounts for that then that is good. – Neil McGuigan Jan 23 '13 at 19:46
3

One idea:

Entity (ID (PK), {common fields})
Customer (ID (PK), EntityID (FK), {other fields})
Supplier (ID (PK), EntityID (FK), {other fields})
Telephone (ID (PK), EntityID (FK), Value)

This also has the added advantage of reducing duplication between Customer and Supplier.

Bernhard Barker
  • 54,589
  • 14
  • 104
  • 138
  • 1
    This is a better implementation – APC Jan 22 '13 at 10:22
  • 1
    Good solution, but beware of overcomplexing problem/solution. If you don't have specific supplier or customer fields. Then just don't create customer, supplier table and add a type filed to entity.. It all depends on requirements. :) – roel Jan 22 '13 at 12:54
  • what if a customer is also a supplier? – Neil McGuigan Jan 22 '13 at 18:39
  • @NeilMcGuigan Then you can have 1 row in Customer and 1 row in Supplier, both with the same EntityID. – Bernhard Barker Jan 22 '13 at 18:45
  • @Dukeling, then you are duplicating info such as their name, which is bad db design. – Neil McGuigan Jan 22 '13 at 18:54
  • @NeilMcGuigan `{common fields}` includes all fields applicable to both a customer and a supplier, thus there is no info duplication (since this is only stored once in `Entity`). This may be a little restricting when you want to have a customer be a supplier with selectively different info or if you want to add more types with different subsets of the fields, where multiple types can have similar info. – Bernhard Barker Jan 22 '13 at 19:11
  • Telephone and Supplier have the same parent type? That doesn't make sense to me. Party Model makes much more sense here. – Neil McGuigan Jan 22 '13 at 19:13
  • @NeilMcGuigan `Entity` is a parent of `Customer` and `Supplier`. `Entity` has a 'has' relationship with `Telephone` (as in an Entity has a Telephone). Maybe `Entity` would've been better named `Person` or `Company`. – Bernhard Barker Jan 22 '13 at 19:14
0

my advise is that you assign an ID for the table then add a reference field with the same datatype with the SupplierID and CustomerID

Mark
  • 8,046
  • 15
  • 48
  • 78
0

The other answers are decent beginner solutions, but they all have the same fundamental flaw in that Customer or Supplier are Relationships and not their own unique entities. A Customer is not a person, it's a relationship between you and a person.

In fact a person could be an Employee, Customer, Supplier all at the same time, or over time.

A Customer or Supplier could also be a Business, with many involved people.

Here is the correct answer:

PARTY
id
type {individual, organization, automated_agent}
org_name null
first_name null
last_name null

PARTY_RELATIONSHIP
from_party_id FK PARTY
type {supplier_of, customer_of, ...}
to_party_id FK PARTY
from_date
to_date null

Usage:

Insert into party (id, type, org_name) values (1, 'organization', 'Raw Steel Co');
Insert into party (id, type, f_name, last_name) values (2, 'individual', 'Davide', 'X');

-- Raw Steel Co is both a customer of and supplier to you:
Insert into party_relationship values (1, 'supplier_of', 2, getdate(), null);
Insert into party_relationship values (1, 'customer_of', 2, getdate(), null);

Now, some people don't like Single Table Inheritance because of the nulls, but goddam it is easier to work with. Use Class Table Inheritance if you're finicky.

The 'type' columns should be foreign keys to type tables.

Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152
-1

you can do like

Customers: Table of customers - CustomerId, Other columns
Suppliers: Table of suppliers - SupplierId, Other columns
Telephones: Table of telephones - TelephoneId,TypeId,TypeName, other columns

where TypeName will be Customers or Suppliers, ant TypeId will be id of the resp.

Dnyan Waychal
  • 1,418
  • 11
  • 27