basics of database design

[erkka]

I'm learning database programming, using TDbf. I'd like to make a simple application for managing customer data & invoices. What I can't quite figure out is how to handle "arrays of records" in databases.

In each invoice, the customer is usually billed for many different items.
Bit simplified, one single invoice could contain data like this:

 customer-id
 date
 #1  item-id, amount, remark
 #2  item-id, amount, remark
 #3  item-id, amount, remark
 ...
 #n  item-id, amount, remark
 total price


Tutorials and books I have read are helpful with basic data-types like date, integer & etc. But what would be the best way to hande the data of items ordered? If I weren't using a database, I would make it a (dynamic) array of records (as number of items ordered is likely to vary from 1 ... 32 or so)

TDbf wiki tutorial says that there is a data-types called ftArray and ftDataSet, but doesn't provide much help what they do and how to use them. Any suggestions where I could find more info?

[eny]

An 'array of records' is called a table

Have a look at relational modeling.

[erkka]

I see,
so then my question is: Can I embed a table inside another table?

as records it would be something like this:

---

type
  invoiceline = record
    item-id : integer;
    amount : integer;
    remark : string;
  end;

  invoicedata = record
    number : integer;
    customer-id : integer;
    invoicelines : array [1..32] of invoiceline;
  end;


var invoices : array [1..MAX] of invoicedata;

---

So what I'm trying to ask is, that can I make something like this
 invoicelines : array [1..32] of invoiceline
with Tdbf?

With Tdbf I can handle tables of integers and strings, but it says that field type ftArray is not yet supported. So am I still missing something very basic, or should I just start using something more powerful istead of Tdbf?

[eny]

I see,
so then my question is: Can I embed a table inside another table?

No.
You use 2 different tables for that and store a unique relation between them.

With Tdbf I can handle tables of integers and strings, but it says that field type ftArray is not yet supported. So am I still missing something very basic, or should I just start using something more powerful istead of Tdbf?

I don't know Tdbf, but the relational principle applies to all relational databases (hence the name).

You'll need a invoicedata table that contains the general information of an invoice ánd a unique id of some sort (integers are often used). Every invoice has it's own unique number.
For every invoice you create one new record in this table.

You'll need an invoiceline table with an extra column: a reference to the invoice; the unique invoice number.
For every invoiceline you create a new record in this table.

Now if you want all invoicelines for an invoice, use the unique invoice number to search all matching rows in the invoiceline table.

etc. etc. etc.

[erkka]

Thank you for your reply.

You'll need an invoiceline table with an extra column: a reference to the invoice; the unique invoice number.
For every invoiceline you create a new record in this table.

OK, so let's assume that I have two invoices.
 Invoice #1 has 4 invoicelines
 Invoice #2 has 2 invoicelines

so I would have invoiceline table with fields "invoice #", "item", "amount", looking like this:

 1 , 6, 4 (customer is invoiced for 4 pieces of item-id 6)
 1 , 11, 2
 1 , 12, 10
 1 , 14, 6
 2 , 4, 8
 2 , 11, 7

Repeating invoice-id for every entry in the table seems a bit like wasted space to me. But if that is the way to do it, I'll go for it.

[eny]

That's the way to do it 

Repeating invoice-id for every entry in the table seems a bit like wasted space to me.

'Wasted space' was in issue in the dark ages when memory was expensive and hard disk capacity was measured in kilos (as in weight) 

[erkka]

'Wasted space' was in issue in the dark ages when memory was expensive and hard disk capacity was measured in kilos (as in weight)   

In my teen-age we were programming with Commodore-64 assembler, and there certainly weren't any hard disks around, neither in gigas nor kilos. 

[eny]

Ah, the good old days.

HD's did exist in those days as can be read here.
You just needed a big house