Design Database - better approach

Question

Hi

I have a table Costumers and another CostumersPayments.

In CostumersPayments I have the type of payments (MB, Visa, so on).

My problem is that each customer can have more than one payment, so I can't create a field called "IDPayment" where I set the ID of that payment, and I don't know how many payments I'll have (the administrator may add or remove).

How do you recommend I store the payment IDs in table Costumers? Creating a field where I store them like an array? ("1, 2, 3" separated by commas).

Thanks

Answer 1

This is essentially a many-to-many relation. A costumer can have many payments types and one payment type can be used from many costumers. Just have a table Costumers, table PaymentTypes, and table CostumersPayments. This table will have two columns - CostumerID and PaymentTypeID. This will be a composite primary key (of the two columns).

CostumerPayments

| CostumerId | PaymentTypeId |
------------------------------
| 1          | 3             |
| 1          | 4             |
| 2          | 3             |
| 2          | 5             |
------------------------------

Edit: Your initial proposition of storing ids in one row, separated by commas, is one of the biggest sins in database design. It violates even the first normal form. Look here for more information.

Answer 2

It sounds more like, given the description above, that each costumer can make multiple payments.

In which case, the Payments table just needs to have the CostumerID in there for each row, surely?

-- EDIT: You'll probably want 3 tables. Costumers, Payments, and PaymentTypes (I try to be explicit about my table names).

In PaymentTypes, you have ID and Name. In Payments, store CostumerID and PaymentTypeID.

Answer 3

Creating a field where I store them like an array? ("1, 2, 3" separated by commas).

This breaks the normalization rules for relational databases. Don't ever do something like this.

Answer 4

You use three tables. Store customer information in "Customer" table with CustomerId as primary key. Store payments in "Payment" table with PaymentId as primary key. Then use a join table called "CustomerPayments" that has a primary key of CustomerId and PaymentId.
You should not store PaymentIds in Customer table as comma separated value. That is not a normalised database. See http://en.wikipedia.org/wiki/First_normal_form

Frankly I hope you are not in charge of developing a commercial business solution with your level of experience.

Answer 5

Just have a column CustomerID in CostumersPayments, and make it a references toward Customer.

If you want to retrieve the Payments for a Customer, just join the tables.

Answer 6

First off, absolutely do not store several values in a single field as you mentioned. That is a bad idea for many reasons, as others have described.

Secondly, I'm not clear what is contained in the CostumerPayments table. Is this individual payments (with an amount) made by the customer? And each one of these payments has a payment type associated with it?

If that's the case you want the following tables:

1. Costumers, with a primary key of CostumerID and no reference at all to payments.
2. PaymentTypes, with a primary key of PaymentTypeID and no reference to either payments or customers.
3. CostumerPayments, with a foreign key of CostumerID pointing back to the Costumers table and a second foreign key of PaymentTypeID pointing to the PaymentTypes table.

To find all payments for a customer, look in the CostumerPayments table WHERE CostumerID = <>. To get customer information and payments in the same query JOIN the two tables Costumers and CostumerPayments. To find the correct name of a payment type, JOIN the PaymentTypes table in as well.

Alternatively, if your goal is to store a list of PaymentTypes each customer is allowed to use (not referencing an amount, just the fact that a customer is allowed to use, for instance, a VISA card) add a table:

CostumerAllowedPaymentTypes, with foreign keys CostumerID and PaymentTypeID into their respective tables.

Answer 7

Thank you for warning me about the normalization rules.

I'll create an third table and store the relations with the columns IDpayament and IDcostumers

Thanks ;)