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).


+2  A: 

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).


| 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.

Petar Minchev
Correct, but please no surrogate key
Yes, here it is not necessary. But in a more complex scenario it could be:)
Petar Minchev
one-to-many, each payment is associated with only one customer.
@pascal I think it is a payment type and can be used by more than one customer. Customer1 pays with MB and Visa, Customer 2 only with Visa and so on.
Petar Minchev
@Petar I took it as a list of credit cards, like the list of payment means associated to an Amazon account...
@pascal OK, but it is not clear from the question:)
Petar Minchev
I'm guessing that the OP is not actually asking the question that this answer (correctly) answers. I'm guessing that CostumerPayments is an actual list of payments, and that the PaymentType thing is in addition to relating the actual payments to the customers.
Larry Lustig
+1  A: 

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.

Stephen Orr
It is a payment type. One payment type can be used from more than one customer.
Petar Minchev
Ah, I understand now. I've edited my answer to be more appropriate.
Stephen Orr

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.


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

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

Your technical advice is good and correct. But I don't think there was any need for the unconstructive comment at the end - we all had to learn somewhere and many years ago when I first came across this sort of design issue I wish there had been stackoverflow to ask about it.
Sorry I did not mean to criticise the poster.It is more towards his company. With all sort of organisations having credit card security breaches I hope the companies have experienced people to guide new developers rather than hoping they pick up by themselves. Security is a tricky thing and consequences are heavy.

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.

+1  A: 

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.

Larry Lustig

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 ;)

Guilherme Cardoso