Need ideas on how to store data in SQL-Database

Hello!

I need to store and manage some data into an SQLite-Database:

- Customers
- Articles
- Invoices

So far I created two QSqlTableModels for Customers and Articles as they are straight forward. I thought of a QSqlRelationalTableModel for the invoices to share the customers and the articles.
But I am not really sure how to store the invoices and their children, the invoice entries (position, quantity, article name, price,...)

Is it common in this case to create a new table for each invoice, and just fill it with the entries? This looks a bit like overkill to me, but would also be straight forward. Well, I have no idea how else this could be accomplished using a database.
Can SQL tables have child-tables, like e.g. QStandardItems can?

Kind regards,
HomeR

Quote:

---

Originally Posted by homerun4711

Hello!

I need to store and manage some data into an SQLite-Database:

- Customers
- Articles
- Invoices

So far I created two QSqlTableModels for Customers and Articles as they are straight forward. I thought of a QSqlRelationalTableModel for the invoices to share the customers and the articles.
But I am not really sure how to store the invoices and their children, the invoice entries (position, quantity, article name, price,...)

Is it common in this case to create a new table for each invoice, and just fill it with the entries? This looks a bit like overkill to me, but would also be straight forward. Well, I have no idea how else this could be accomplished using a database.
Can SQL tables have child-tables, like e.g. QStandardItems can?

Kind regards,
HomeR

---

No, don't do that. It's hard to say exactly what you need without knowing your full problem, including all the data you must store, but the usual way of solving problems like this, where you have one thing containing 0...* "line" entries, is to have at least two tables. Something like so:

Customers
id | name | ....etc....

Articles
id | ...?

Invoices
id | cust_id | single items...

Invoice line
invoice_id | line items...

You then use a JOIN during your SELECT queries to merge the two tables:

SELECT * from Invoices,Invoice_Line where Invoices.id = Invoice_Line.invoice_id;

Or something like that...forget the exact syntax.

Try to search for some info on "Database normalization".

Quote:

---

Originally Posted by stampede

Try to search for some info on "Database normalization".

---

Very good hint into the right direction. Thanks.

Quote:

---

You then use a JOIN during your SELECT queries to merge the two tables:
SELECT * from Invoices,Invoice_Line where Invoices.id = Invoice_Line.invoice_id;

---

Jup, as far as I read by know, this is exactly what database normalization is about. I will solve it that way.

Thanks for the good answers!