I'm doing a project to manage membership and other kind of payments, but mainly membership so I created a polymorphic schema. any idea, improvement, for some reason I don't fully convinced about the schema.

as you will see, the idea of having month, year NULL-ABLE is allow save record of any other payment

11-digit months are an interesting concept; ditto 11-digit years. It is not clear that you record the actual date that the payment was made - as opposed to the date that the payment applies to. What happens if someone sends two checks for a month (typically because they sent a cheque (check) for, say, $50 forgetting that the correct amount is $60). You've not stipulated any foreign keys. Overall, your schema is not yet complete.
–
Jonathan LefflerMay 9 '09 at 18:57

if you need add another check, only need to do is create a new order, and that´s it. regarding months and years the idea is that some partner can pay some month and year different to the current. for exameple image you want to pay 6 months of membership, you will have one order with 6 payments. one per month. I hope this clarify this.
–
Gabriel SosaMay 9 '09 at 23:50

2

@Jonathan Leffler: The int(11) is an artifact of MySQL's metadata output. The number has nothing to do with the size of the integer; an integer is always 32 bits regardless of the argument. The argument applies only to zero-padding the integer value when using the ZEROFILL column option. This is a common misconception about MySQL.
–
Bill KarwinMay 21 '09 at 1:33

I would consider making month an enum in this case. It could certainly remove all ambiguity, unless you need to do math on that field (was this earlier that).

The money should be stored as a Decimal, not a float. Weird rounding stuff will creep in if not.

There is no concept of the price of an order. If they underpay, how will you know by how much?

(Kind of related to 3) You would typically see this as an invoice and payment type representation (even if you don't issue invoices), so that would imply that one payment could represent more than one order, and vice-versa, so that would imply a many to many relationship.

Do you care how they paid? (Check, Credit Card, Cash, etc.?)

Why would you make an order with multiple payments if they can only be received one in a month? What would you do if payments are received within the same month? Should there really be just a one-to-one relationship here?

One other note, our convention is to name Entity tables in the singular, matching the class name. That is, we name one row (we name one instance of the class) rather than naming the set. The question we ask is, one row in this table represents one what? And we use that singular name for the class and for the table. (Anticipating the objections, yes, I do recognize that other developers and other frameworks follow the 'pluralize the table name' convention. Rails is even smart enough at pluralization to generate a "people" table from a Person class.)

But when table names start getting pluralized, I notice that often only some of the table names get pluralized, and it ends up being a mix of singular and pluralized names.

`partner_id`
`order_id`

Your foreign key columns are named exactly the way we would name them. The convention we follow for a foreign key column is to use the name the parent table, followed by _id. For multiple relationships to the same table, we use the name of the role in addition to or in place of the table name.

I would also suggest adding the foreign key constraint definitions in the database, even if the MyISAM engine doesn't enforce them.

Add a primary key constraint on the ID column on each table (it seems to be missing from the partner table.

Identify natural keys with a unqiue index.

It seems to me there are two models for payments:

one payment in full for each order

This is the model that Amazon seems to use. There may bonus coupons and credits applied to an order, but when it comes down to the payment, I make exactly one payment for the order.

payments made to an account balance

The other model is to use an account, and to apply charges, credits and payments to the account. This is the model commonly used by utilities like the telephone company. This allows for concepts like current balance and amount due.

Your design seems unconventional in that respect. There's no notion of a customer account. Yet, it seems like there will be multiple payments for one order.

nice distinction for payment models - I'd almost always go for a separate account that will be balanced against due payments unless there is a very strong reason not to do so.
–
Olaf KockMay 15 '09 at 20:52

I think the account model is much more common. Even with the simple Amazon example, they still track customers and accounts. (They have to have a way to apply returns and other credits. On the other hand, a street vendor with a hotdog cart would be much more likely to handle payments on a per order basis. For a simple shopping cart application (does the world actually need ANOTHER shopping cart application?) the one payment per order may work.
–
spencer7593May 15 '09 at 21:52

Adding payment_date to payments would let you get rid of orders.last_check_on in favor of a view

Is there no identifying info for a payment? Check # or something? Duplicate entries seem like they'd be a problem here...

payments.month and payments.year seem oddly placed. If this is for a membership system, I'd assume you would just pay-as-you-go. So, 6 payments would get you 6 concurrent months of membership - starting on the order date. There's no need to track what month a payment is for (otherwise, what does it mean when I pay for months 6 and 7, but not 1-5?) If there's some more complexity here, it may be another table or two to hold that concept.

...a project to manage membership and other kind of payment

type enum('membership','other')

the idea of having month, year NULL-ABLE is allow save record of any other payment

I may be off base here, but it sounds like you're trying to guess on future requirements. If that's the case...DON'T. There is no one-size-fits-all database schema - so don't compromise the application you're building for the application that you may or may not build in the future.

If you have concrete use cases outside of membership, then share them. But, I have a feeling that it'd be best served with 2 different models. Type and nullable columns usually scream that you're trying to shoehorn unlike things into the same table.