Is this good membership payment database schema?

Question

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

CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(11) NOT NULL auto_increment,
  `partner_id` int(11) NOT NULL,
  `status` enum('pending','accepted','cancelled','other') NOT NULL,
  `created_on` datetime NOT NULL,
  `concept` varchar(250) NOT NULL,
  `type` enum('membership','other') NOT NULL default 'membership',
  `source` enum('dineromail','casati','deposit','other') NOT NULL default 'dineromail',
  `notes` text NULL,
  `last_check_on` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  ;


CREATE TABLE IF NOT EXISTS `payments` (
  `id` int(11) NOT NULL auto_increment,
  `order_id` int(11) NOT NULL,
  `month` int(11) default NULL,
  `year` int(11) default NULL,
  `amount` float NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx-order_id-month-year` (`order_id`,`month`,`year`)
) ENGINE=MyISAM ;


CREATE TABLE IF NOT EXISTS `partners` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `last_name` varchar(255) default NULL,

) ENGINE=MyISAM;

Answer 1

I would also include a date time stamp for when the payment is received and possibly mark if a payment is complete or incomplete amount.

Answer 2

Also, I'd have the source in a different table as you might need to add a few sources when the application grows.

Answer 3

A few commnets:

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

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

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

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

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

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

Answer 4

I concur with the previous answers from jtyost2 and Yishai.

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.

Answer 5

• Ditto the decimal instead of float for money amounts.
• Where's the order amount? How do you know if they owe money or not?
• 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.

Answer 6

CREATE TABLE [dbo].PaymentLog(
TransactionNumber int IDENTITY(1,1) NOT NULL,
ReferenceID int NOT NULL,
ReferenceType varchar(20) NULL,
TransactionID int NULL,
CustomerID int NULL,
PaymentMethod char(4) NULL,
LogType varchar(20) NULL,
UserHostAddress varchar(20) NULL,
Content nvarchar(4000) NULL,
ReasonCode varchar(20) NULL ,
Flag nvarchar(20) NULL ,
Note nvarchar(200) NULL,
[InDate] [datetime] NOT NULL CONSTRAINT DF_PaymentLog_InDate DEFAULT (GETDATE()),
[InUser] nvarchar NULL,
CONSTRAINT PK_PaymentLog PRIMARY KEY CLUSTERED
(
TransactionNumber
)
) GO

CREATE NONCLUSTERED INDEX [IX_PaymentLog_ReferenceID] ON [dbo].PaymentLog
(
ReferenceID ASC
)WITH FILLFACTOR = 90
GO