I have a table with acts payable records that basically have vendor ID,
Invoice Number, Invoice Date, Invoice Total. How can I create another table
that would have several records of data pertaining to above record that would
attach itself to the record. (Invoice Header / Invoice Data). Thank you.
That depends entirely on a) what the table would contain and b) what you plan
to do with it.
As a rule, the hair on my neck starts to stand up when I see a fieldname like
[Invoice Total]. With very, very few exceptions, totals should never be stored
in a table; they should be calculated on the fly (e.g. by summing the records
in an InvoiceDetail table).
In any case you're perhaps starting this design in the middle. Relational
design starts with you sitting out of reach of your computer keyboard, with a
pad of paper and a #2 pencil, identifying the real-life Entities (people,
things, or events) of importance to your database. Each type of Entity gets
its own table, with Fields for each distinct, nonrepeating, atomic chunk of
information - "Attributes" is the jargon term - of importance to your
application. When you have defined these tables, you would identify how they
are related, logically, based on the meaning of the data (e.g. each Invoice
involves the sale of one or more Products; each Product may appear on zero,
one or more Invoices; a Product cannot appear more than once on the same
Invoice but may appear in multiple Invoices). Only then do you start actually
creating tables!
