Database design/normalisation query

Hatter The Mad · 1 Jun 2012 at 19:27

I am designing a database of orders. An order can either be a monthly subscription type thing, or a pay-once type thing. This means that each order will have different properties depending on the type.

Order properties = columns

Is it best practice to create one order table with all of the columns for order properties of all types, or to create an entirely separate order table for each order type, with specific columns for properties as appropriate?

Goksly · 2 Jun 2012 at 01:09

I'd personally create an orders table (common properties) and then one table per type (specific columns). I'd then have the primary key of the common table as the primary and foreign key for the sub tables.

slylittlefox · 2 Jun 2012 at 01:25

Agree with Goksly I think (it's late

), though I'd also add a 'type' field or similar in the main orders table so you can identify which order sub-table you have to query.

Stretch · 2 Jun 2012 at 01:25

The rule is never duplicate data without very good reasons e.g performance. As Golksly said, create an orders table and join to a case specific table where required. This comes with it's own problems, but not as big as having to maintain two copies of the same data.

Stretch · 2 Jun 2012 at 11:33

Actually, is there really any difference between pre-pay and pay monthly orders beyond how/when they pay? It's still an order, they still have a subscription start and end date, just one will have many "payments" over the subscription period and the other will only have a single payment at the beginning. They are both still "ordering" the same thing, it's the payments that will be recorded differently.

Hatter The Mad · 2 Jun 2012 at 11:55

Stretch said:
Actually, is there really any difference between pre-pay and pay monthly orders beyond how/when they pay? It's still an order, they still have a subscription start and end date, just one will have many "payments" over the subscription period and the other will only have a single payment at the beginning. They are both still "ordering" the same thing, it's the payments that will be recorded differently.

Yes, because 'pre-pay' is actually 'pay once and then optionally renew to get the latest version'

touch · 4 Jun 2012 at 13:53

Hatter The Mad said:
Yes, because 'pre-pay' is actually 'pay once and then optionally renew to get the latest version'

What are the different properties that each order type has though?

Both will have a 'next payment due' or 'subscription expires' date, which will have a different date entered depending on whether they are monthly or yearly subscriptions. Other than that, i cant think of any other details that would be different?

RimBlock · 6 Jun 2012 at 07:08

Off the top of my head...

Tables
Orders
Subscriptions
Invoices
Payments
Products

Orders.
OrderID, Date, CustomerID, SubTypeID (if subscription or NULL if not), ProductID, Value

Subscriptions.
SubTypeID, Start, Length (i.e. 12/24), Unit (i.e. months/years)

Invoices (Sent).
InvoiceID, OrderID, Date, InvoiceValue, DueDate, Received

Payments (received)
PaymentID, InvoiceID, , DateReceived, Amount

Products
ProductID, .........

Extending a subscription or original non-sub order generates a new order rather than amending the original.

An order will generate an invoice based on the cost or based on the cost div subscription type if it is a subscription.

You will need to go over the above thoroughly as it is meant as a starting point rather than as the final solution

. I take no responsibility for any damage caused by using any of the advice in this, or any of my, posts

.

RB

Pho · 6 Jun 2012 at 07:54

Using something like RimBlock's idea you could also look into using something like a cron to automatically create orders from subscriptions when due.

Being able to have just one table for something you need to join to often (e.g. orders) will save a lot of hassle otherwise you'll be doing lots of unions when you need to get all orders at the same time.

touch · 6 Jun 2012 at 11:22

I would create the orders table like this:

OrderID (unique idenfitier of order)
Date (date of order)
CustomerID (link to customer)
ProductID (link to product, assuming an order is for a subscription to 1 product only)
RecurringFee (monthly or yearly cost)
NextPaymentDue (date of next payment)
SubscriptionEnd (date subscription ends)

All of these fields will be the same for each order type. If yearly orders are forced to renew after a year, the 'NextPaymentDue' and 'SubscriptionEnd' date would be a year from now. If they are not forced to renew, they would be null.

djkav · 6 Jun 2012 at 12:11

I'd modify RB's slightly...

Tables
Orders
PaymentTypes
OrdersPaymentTypes
OrdersOrderProperties
OrderProperties
Invoices
Payments
Products

Orders.
OrderID, Date, CustomerID, PaymentTypeID, ProductID, Value

PaymentTypes (Allows for more future payment types)
PaymentTypeID, Title, Description

OrdersPaymentTypes (Orders - Payment types Lookup)
PaymentTypeID, OrderID (can be a composite key, as no duplications allowed)

OrdersOrderProperties (Orders - Order properties Lookup)
OrdersOrderPropertyID, OrderID, OrderPropertyID, Value

OrderProperties (Allows for custom fields specific to certain orders and payment types. Such as, Start, Length (i.e. 12/24), Unit (i.e. months/years)... Other custom fields specific to certain orders and payment types.)
OrderPropertyID, Title, Description

Invoices (Sent)
InvoiceID, OrderID, Date, InvoiceValue, DueDate, Received

Payments (received)
PaymentID, InvoiceID, , DateReceived, Amount

Products
ProductID, .........