Newb needs help with relationship schema

[TurboSi]

I am by no ways or means a database programmer but I understand most concepts enough to create what I need to. But I admit I'm always stumped on what is probably a straight forward concept for most of you.

I have the typical company (just one) with typical customers (many) creating quotes/invoices (many) with plenty of items for said invoices. I have no problem with creating a master invoice table with relationships to customer table and a relationship to the items table (and some of the items have relationships to subitems)...but I really don't know the correct way to tie a single company record to all of this.

Right now since the company record is just name/address and some misc variables I use global storage, create a unique ID field and use auto-enter of that unique number into the foreign key field of the other tables. somehow I don't think this is correct so what would be the proper way to do it or is there a good example somewhere I could rip apart and get a really good feel for the correct way to do things?

any help and advice is much appreciated!

[comment]

Right now since the company record is just name/address and some misc variables

What do you expect to happen later on?

[TurboSi]

Probably very little as far as company records go. Possibly changes to phone/email or changes in tax rate.

[mr_vodka]

You can have a single record "preferences" type of table to store this data. Typically when reselling a product you would have this to store the company data, logo, address, phone etc. This table can be related to the other tables using a Cartesian (X) join or you can have them loaded into globals via a startup script.

As for tax rate, it would be based on customer if dealing with multiple tax rate scenarios. If the is a POS system though then you could store this in the preferences table as well.

[comment]

Assuming you want each invoice to store the phone/email as it was at the time the invoice was issued (and you definitely want each invoice to store the applicable tax rate), define those fields to auto-enter the data from the global fields.

Another option is to have a one-record Preferences table, and look up the data from there. This makes it easier to change the preferences while the file is being served - but it also requires either a relationship between Preferences and Invoices (and any other table using them), or a startup script to load the preferences into global fields/variables.