Is this a good payment card database schema?

Question

I am doing a project to manage membership and other types of payments, but mainly for membership, so I created a polymorphic scheme. any idea, improvement, for some reason, I'm not completely convinced of the scheme.

as you will see, the idea of ​​having a month, a year NULL-ABLE allows you to save a 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 timestamp of the date on which the payment was received, and perhaps mark if the payment is completed or an incomplete amount.

Answer 2

Multiple commnets:

• . , , , ( ).

• , . , .

• . , , ?

• (, 3) - ( -), , , , - .

• , ? (, , ..?)

• , ? , ? " " ?

Answer 3

jtyost2 Yishai.

: , Entity , . ( ), . , , - ? . ( , , , "pluralize the table name". Rails "people" Person.)

, , , .

`partner_id`
`order_id`

, . , _id. .

, MyISAM .

(, , partner.

unqiue.

, :

, Amazon . , , , .

• ,

- , . , , . , .

. . , , .

Answer 4

• , float .
• ? , ?
• payment_date payments orders.last_check_on
• ? # - ? , .
• payments.month payments.year . , , . , 6 6 - . , ( , , 6 7, 1-5?) , , .

...

type enum ('membership', 'other')

, NULL-ABLE

, , , . ... . , , , .

, . , . type , , , .

Answer 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

Answer 6

In addition, I will have a source in another table, as you may need to add several sources when the application grows.