Thank you aLbert for your feedback. You are correct
about their only being one customer but my problem is
that the customer may receive a quote or many quotes but
not necessarily go into a job or there may be a job
without the quote.
The above tells me that a customer can have a quote. Do you need, or want to
attach jobs to a quote?
If 99% of jobs have to be attached to a quote, then obvsilty for those RARE
exctips, you would create a quote, and not fill much of it out,and then
simply attach the job to the not very usefaull quote part. But, who cares
for these rare exctipns. (and, only you know the answer to this qutstion). A
big part of data modeling is to attempt to come up with a desing that
models the problem at hand. And, since we don't have un-limnited programmer
resouces here, then often a compromose needs to be made. So, while some jobs
don't need a quote..do most? If you say 50/50, then lots of quotes will be
made, and lots of jobs can be made, and thus the desing should reflect this
relaidty.
Lets assume that the answer is 50/50 (jobs often belong to a quote, but
often they don't). that means we would choose:
Customer ->Quotes
->jobs
Thus, we need 3 tables, but quotes and jobs relatte back to customer. We
also would of couse put in the jobs reocrd what Quote the job belongs to IF
a quote is needed.
I hope this helps. Can you please explain to me exactly
how the
linking tables work with the combined primary codes.
Should they have a special name. I think I have them
linked together correctly as one primary key:
I have always used the defalut for primary key id's. In this case, your
customer table likey has a ID field called ID.
Line1(Index Name) Customer ID (Field Name) Customer ID
Ah, ok..you used Customer ID. I find spaces in field namesx shold be
advoied..they are a souce of pain, and problems.
up with Quotes and Jobs. What do I use as the Primary
Key from the linking table?
Each child table (the many side) simply need to have a field added that
tells us what Customer Reocrd id it belongs to. So, in each chidl table you
use a REGULAR long integer field. (it is likey important to note that
relatosions in child tables are simply reuglar field, and are not autonumber
filds, nor are then primary key field, nor are they speical fields. The
fields used to build relatonations in these child ables are simply regular
long integer fields. We often call these regular plain jane fields Forigher
keys.
Should this Combined Prinary
Key have a special name?
Huh? When did we start talking about combined primary keys. What do we need
them for?
You got a table called jobs. If you need to relate this table back to
Customer, then we simply add a reagular field to the jobs table. A good
field name would be Customer_Id. Note that our jobs table does not actaully
need a primary key. (you can certanly create a primary key field in the jobs
table, but we don't need it..do we?). Also, since we cna have a LOT of
tables, then you might as well standarzie your naming,a nd make all primary
key fields have the simple name of ID. For those simply plain fields that
are used in relatons, then you can use
tableName_ID
Anyway, you already made some tables...so don't worry about what you used
for your primary key fields (but, these fields are going to autonumber
fields).
You also statned that you would like sometimes to relate the jobs to a quote
(but not always). Ok, then simply add another field to the jobs table, and
that field is going to be a regular long integer field that relatts to the
quotes table. We can call tha tfiedl
Quotes_id
I am not sure about what will
happen to this link for the next step.
it turns out, that linking MUST be done by you! In other words, after
setting up all this way cool relatonship stuff, YOU STILL have to entry the
valeus into the fields used for linking to a parent table (note how I said
linking to a parent table, as you do not link the other way! (but you can,
and do think conceptilay that the link is from the parent to the child
table....yes, this is true..but the actual field used in the link is in the
child table (our so called forightn key)).
So, you can open up a form, and add anew customer. While adding the
customer, you can make note, and write down the CustomerID. You can then
open up the Quotes table, and enter in the quotes information, and to link
this quote to a particular customer, YOU HAVE to enter the customerID into a
field. Of course, as you become a better developer, then you would make the
application set the customerID, but I do want to stress that it is YOU who
has to enter this number into the table to make it relate back into the
customer table.
And, if you make quotes a sub-form, then ms-access *CAN* enter the
customerId into the quotes "customer_id" field for you (you still have to
set what field you used).
When you add a new job, then you HAVE to enter the Customer_Id field, but
the Quotes_id field is of course optional....
