这是一个好的会员支付数据库架构？

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

正如您将看到的,拥有月,年NULL-ABLE的想法允许保存任何其他付款的记录

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;

6 个解决方案

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

我还会在收到付款时附上日期时间戳,如果付款完成或金额不完整,可能会标记。

#2

A few commnets:

一些共同点:

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.

(与3相关的类型)您通常会将此视为发票和付款类型表示(即使您不开具发票),这意味着一次付款可能代表多个订单,反之亦然,因此这意味着多对多的关系。
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?

如果只能在一个月内收到一个订单,为什么要订购多笔付款?如果在同一个月内收到付款,您会怎么做?这里真的应该只有一对一的关系吗?

#3

I concur with the previous answers from jtyost2 and Yishai.

我同意jtyost2和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.)

另外注意,我们的约定是以单数形式命名实体表,与类名匹配。也就是说,我们命名一行(我们命名该类的一个实例)而不是命名该集合。我们问的问题是,这个表中的一行代表一个什么?我们在课堂和桌子上使用这个单数名称。 (预测反对意见,是的,我确实认识到其他开发人员和其他框架遵循'复数表名'约定.Rails在复数化方面甚至足够聪明,可以从Person类生成“people”表。)

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.

您的外键列的命名方式与我们命名它们的方式完全相同。我们对外键列遵循的约定是使用父表的名称,然后使用_id。对于同一个表的多个关系,我们使用角色的名称作为表名的补充或替代。

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

我还建议在数据库中添加外键约束定义,即使MyISAM引擎没有强制执行它们。

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

在每个表的ID列上添加主键约束(伙伴表中似乎缺少该约束)。

Identify natural keys with a unqiue index.

使用unqiue索引标识自然键。

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.

在这方面,你的设计似乎是非常规的。没有客户帐户的概念。然而,似乎一笔订单会有多笔付款。

#4

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

将payment_date添加到付款可以让您摆脱orders.last_check_on以支持视图

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.

payments.month和payments.year看起来很奇怪。如果这是一个会员制,我会假设你只是按现收现付。因此,6个付款将为您提供6个并发的会员资格 - 从订单日期开始。没有必要跟踪付款的月份(否则,当我支付第6个月和第7个月而不是1-5个月时它意味着什么?)如果这里有更多的复杂性,可能需要另外一两个表那个概念。

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

有月,年NULL-ABLE的想法允许保存任何其他付款的记录

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.

如果您有成员之外的具体用例,请分享它们。但是,我觉得最好配两种不同的型号。类型和可空列通常会尖叫,你试图与同桌中的东西不同。

#5

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](100) NULL,  
    CONSTRAINT PK_PaymentLog PRIMARY KEY CLUSTERED (  
        TransactionNumber  
    )   
)
GO

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

#6

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

此外,我在不同的表中有源,因为您可能需要在应用程序增长时添加一些源。

#1