具有静态值和动态值的MYSQL表[英] MYSQL Table with static values and dynamic values

本文是小编为大家收集整理的关于具有静态值和动态值的MYSQL表的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

在我的网站上,我正在创建一个发票功能. 发票具有静态信息:公司信息和接收器信息. 但它也具有动态信息:小时数,描述,总金额等. 客户可以在上面的动态信息中添加多个行.

现在我的问题是, 如何将其实现到我的数据库中?

目前,我有一个名为"发票"的表,其中列将包含上面所有信息. 但是,这样,行将具有不必要的信息,例如公司和接收器信息,实际上只需要每个发票一次插入一次.

你们如何认为我必须解决这个问题?

我想避免使用2个表,但是如果没有其他选择,我会.

问候

推荐答案

如果您想要一个完全的关系数据库,则需要创建第二个表.这将拥有客户信息.然后,此键成为发票表中的外键,因此您只能存储一次客户信息.这将是推荐的实施方式.

另一种方式(我不建议这样做)是您需要对"父"发票(存储客户信息)创建一个循环引用.然后,每次创建新的发票时,都必须插入该父记录ID,因此您可以检索存储的信息.对于同一客户的所有其他发票,您只需将这些信息留空.

其他推荐答案

选择每个基础表的语句

您只拥有一张桌子的愿望是不知情的和不明智的.

每个表都有有关应用程序情况的基本声明.使语句成真的行在表中.

ivoice(i,c,...) // invoice [i] is to company [c] with ...
company(c,n,a,...) // company [c] has name [n] and address [a] ...
receiver(r,...) // receiver [r] ...

因此,每个现在的行做出了一个真实的语句(或者将不存在),并且每个缺席的行会做出错误的语句(或者存在).因此,您可以将表格视为从当前行制作陈述,以及缺席行中的陈述的内容.基础表格在一起描述您的应用程序状况.

limit'和'基本表语句中的'

基本思想是从您的语句中删除大多数和.如果表语句是较小的语句,则应始终像:

(1) something AND-free about [k1],...

列k1上的每个子行,...在表中是唯一的,没有包含较小的子行,或者是:

(2) [c]=some_function_of_all_of(k1,...)

(这称为"第五正常形式".)

为什么要限制'?

否则您的表将有一个有问题的陈述,例如:

,例如:

blah [k1] blah [k2] AND blub [j1]

当" blah zz blah 88"是正确的或错误时,通常不仅要将一堆排进去或从这个桌子中放出一排,根据" blub [j1]"的真相每个可能的J1值. (称为更新异常.)

另外,您可能想说" blah vv blah 999"是真或错误的(在桌上或桌子上),而无需对J1吹牛.但是您选择的桌子不会让您. (称为错误的设计.)

ps:每个查询有一个语句

每个查询表达式也都有一个语句.查询的值是使其陈述成真的行.通过关系操作员组合表以使查询通过逻辑运算符结合语句以发表其声明.加入,在表格上和何处以及陈述的陈述,表的联合会或陈述,选择"有"等等.

//  there is a name n,... where:
//      invoice [i] is to company [c] and ...
//  AND company [c] has name [n] and address [a] ...
SELECT i,c,a FROM company JOIN invoice ON company.c=invoice.c

本文地址:https://www.itbaoku.cn/post/597354.html

问题描述

On my website I'm creating an invoice function. An invoice has static information: Company info and Receiver info. But it also has dynamic info: Amount of hours, Description, Total Amount etc.. Clients can add multiple rows with the dynamic info above.

Now my question is, How do i implement this into my database?

At the moment i have a table called "invoice", with columns that will have all the information above. But in this way, rows will have unnecessary information like the company and receiver info, that actually only has to be inserted once per invoice.

How do you guys think I will have to solve this?

I would like to avoid using 2 tables, but if there is no other option, then i will.

Greetings

推荐答案

If you want a fully relational database, then you will need to create a second table. This would hold the customer information. This key then becomes a foreign key in your Invoice table, so you only have store the customer information once. This would be the recommended way of implementing something like this.

The other way (and I don't recommend this) is that you would need to create a circular reference to the "parent" invoice (the one that stores the customer info). Then, each time you create a new invoice you would have to insert that parent record ID, so you have a way to retrieve the stored information. For all other invoices for the same customer, you would simply leave that information blank.

其他推荐答案

Choose a statement per base table

Your desire to have just one table is uninformed and ill-advised.

Each table has a basic statement about the application situation. The rows that make the statement true go in the table.

ivoice(i,c,...) // invoice [i] is to company [c] with ...
company(c,n,a,...) // company [c] has name [n] and address [a] ...
receiver(r,...) // receiver [r] ...

So each present row makes a true statement (or it would be absent) and each absent row makes a false statement (or it would be present). So you can think of a table as making the statements from present rows plus the NOTs of the statements from absent rows. Together the base tables describe your application situation.

Limit 'AND' in base table statements

The basic idea is to remove most of the ANDs from your statements. If a table statement is the AND of smaller statements then they should always be either like:

(1) something AND-free about [k1],...

where every subrow on columns k1,... is unique in a table and no contained smaller subrow is, or like:

(2) [c]=some_function_of_all_of(k1,...)

(This is called "5th normal form".)

Why limit 'AND'?

Because otherwise your table would have a problematic statement made from one of those simpler ones, like:

blah [k1] blah [k2] AND blub [j1]

When "blah ZZ blah 88" is true or false, you are generally going to have to put not just one but a bunch of rows in or out of this table, according to the truth of "blub [j1]" for every possible j1 value. (That is called an update anomaly.)

Also, it is likely that you want to say that "blah VV blah 999" is true or false (in or out of the table) without blubbering about j1s. But your chosen table won't let you. (That is called the wrong design.)

PS: There's a statement per query

Every query expression also has a statement. A query's value is the rows that make its statement true. Combining tables via relation operators to make a query combines statements via logic operators to make its statement. JOIN, ON and WHERE for tables does AND of statements, UNION of tables does OR of statements, SELECT does a THERE IS, etc. So when you query you are building up more complicated statements from base table statements.

//  there is a name n,... where:
//      invoice [i] is to company [c] and ...
//  AND company [c] has name [n] and address [a] ...
SELECT i,c,a FROM company JOIN invoice ON company.c=invoice.c