需要帮助设计我的发票数据库结构 [英] Need help designing my invoice db structure

问题描述

我有一个客户可以在其中购买订阅的网站.
客户可以随时查看付款历史记录，并查看已购买的商品.

I have a website where customer can buy subscriptions.
The customer can at any time go to payment history and see what has ben bought.

我正在尝试设计用于创建发票的数据库，但是某些东西对我来说似乎不合适.

I'm trying to design the db for creating invoices, but something doesn't seem right for me.

我当前的设置如下:

+-----------+--------------+---------+
|  Invoice  | invoice_item | product |
+-----------+--------------+---------+
| id        | id           | id      | 
| fk_userID | desc         | name    |
|           | quantity     | price   |
|           | sum          |         |
|           | fk_invoiceID |         |
+-----------+--------------+---------+

invoice_item具有引用到product的外键似乎是合乎逻辑的.
但是，如果删除产品会怎样?如果它们相关，则item_list中的行将被删除或设置为null.

It seems logical that invoice_item has a foreign key referenced to product.
But what happens if a product is deleted? If they are related, then row in the item_list will be deleted or set to null.

如果您想查看旧发票并且该产品不再可用，那将不起作用.

And that will not work if you want to look at an old invoice and the product is no longer available.

那么，Product和Item_list应该相关吗?

推荐答案

一旦定义了产品，您将无法删除该产品，因此请向该产品添加一个状态字段-在本示例中，我将使用枚举，尽管它可以很容易地是一个INT或一组布尔值(即已归档)，我使用参数枚举表，但这是一个单独的答案.

You cannot remove a product once it has been defined, so add a Status field to the product that - in this example I'm using an enum, although it could easily be an INT or a set of bools (i.e. Archived), I use Parameter Enumeration Tables for this but that's a seperate answer.

最重要的是要确保发票行在订购时具有从产品中获取的价格(和说明)，以确保将来的任何价格更改或产品名称更改都不会影响预先存在的发票

The most important thing is to ensure that the invoice line has the pricing (and description) taken from the product at the point of order, to ensure that any future pricing changes or product name changes don't affect pre-existing invoices.

我使用过的另一种技术(成功地)是在数据库中引入取代的实体的概念-这样就可以保留原始记录，并在更改数据时插入新版本.为此，我添加以下字段:

The other technique that I have used (quite succesfully) is to introduce the concept of superceding entities in a database - so that the original record remains and a new version is inserted whenever data is changed. To do this I add the following fields :

• currentID
• supersededById
• previousId

这使查询更加麻烦-但特别是对于地址，必须确保发票保持不变并且地址更改不反映在发票中-例如更改公司名称不应更改以前筹集的发票.

It makes the queries a little more cumbersome - but especially for addresses it is essential to ensure that the invoices remain constant and that address changes aren't reflected in the invoices - e.g. changing company name shouldn't change previously raised invoices.

CREATE TABLE `Invoice` (
`id` INTEGER NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`id`)
);

CREATE TABLE `Invoice Item` (
`id` INTEGER NOT NULL AUTO_INCREMENT ,
`desc` VARCHAR(200) NOT NULL ,
`value` DECIMAL(11,3) NOT NULL ,
`quantity` DECIMAL(11,3) NOT NULL ,
`total` DECIMAL(11,3) NOT NULL ,
`fk_id_Invoice` INTEGER NOT NULL ,
`fk_id_Product` INTEGER NOT NULL ,
PRIMARY KEY (`id`)
);

CREATE TABLE `Product` (
`id` INTEGER NOT NULL AUTO_INCREMENT ,
`Price` DECIMAL(11,3) NOT NULL ,
`Name` VARCHAR(200) NOT NULL ,
`Status` ENUM NOT NULL ,
PRIMARY KEY (`id`)
);

ALTER TABLE `Invoice Item` ADD FOREIGN KEY (fk_id_Invoice) REFERENCES `Invoice` (`id`);
ALTER TABLE `Invoice Item` ADD FOREIGN KEY (fk_id_Product) REFERENCES `Product` (`id`);

这篇关于需要帮助设计我的发票数据库结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！