John John - 4 years ago 132
SQL Question

MySQL + PHP data/codescheme design multiple differentiations

I am trying to design a data structure where the class/table has multiple differentiation's withing it itself. I am not sure what to call this or if I use the right terminology.

Hopefully I can illustrate my problem with the following example:

There is a webshop and somebody buys something and goes to the check out. At the check out they can select their payment type. The customer selects cash and with what currency.

An other customer does the same but pays online with credit card.

Now I have the following mysql scheme for this situation:

**Order**
order_id
customer_id
is_paid
payment_type_id

**payment_type**
payment_type_id
payment_name


And a customer table with the usual information. But where do I save if was paid and if required information about how the customer is going to pay.

How would such a database look like and what would the queries look like?

I am not a fan of a little field within tables where the data could be anything depending on the type (or is that the way to go?) cause of querying problems.

Hopefully I am clear enough in what I am asking.

Answer Source

If you have a single payment for each order you want to have it attached to it, so the 'is_paid' is just fine there, as well as the payment type. In this case you want the currency there also. Cause it is independent of the payment type.

I only see problems if you want to know more info about the payment like when it was paid or if you consider a payment for more than one order at a time or even if a payment don't happen at the same time as the order and you want to separate the concepts.

To solve the 1st instead of is_paid you can have a datetime field that when it's NULL is not paid. For the 2nd and 3rd you would need some Payment entity also. For a richer model with some logic about the payment I would have something like:

**Order**
id
payment_id
...
(all sort of info about the order)

** Payment **
id
customer_id
payment_type (or payment_type_id)
payment_value
payment_currency
payment_date
...
(more info about the payment)

Depending on what you'll do with payment_type you can consider having only a value describing the type or an id for the table with more info about it as you already had.

However this will be an overkill if you just have Order 1..1 Payment, in that case just store payments info as you had, along the order.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download