MYSQL Table with static values and dynamic values

Question

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

Answer 1

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.

Answer 2

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