boophoenix
Registered User.
- Local time
- Today, 06:04
- Joined
- Jan 10, 2010
- Messages
- 18
I've been wrestling with this for a little bit now and after a lot of searching and reading think I found my answer, but would like to double check. If I have this correct thank you very much Boblarson for the answer! It's the simple stuff that nails us newbies to the floor or me anyways been trying to nail down an answer to this for two weeks.
If I'm understanding properly the samples I've seen where some use the PK in the next table as the FK and others do not it's basically up to me to decide. To me it's a give me that this Lot is in this Sub and is owned by this Customer.
Table: Customers
CstID (PK)
CstName
CstAddress
Ext....
Table: Sub
SubID (PK)
SubName (FK) to CstID
Ext......
Table: Lots
LotID (PK)
LotNum (FK) to SubID
EXT......
If I have this correct down the road when I get that far I'd like things to be all linked to where the Each Lot is owned by the Sub table and the Sub table is owned by the Customer Table ( if owned is the proper term or close ). For the record this is leading to invoicing and job tracking if I can make it that far.
I would like to be able to do a report or something by Customers total ( all Subs and lots ), Subs ( all lots to particular Customer or just the sub over all as there may be more than one customer in each Sub ), and lastly just the plain old lot total. Ooooops and the big number all customers,subs, and lots totaled. Almost forgot there may be instances where there is just a customer as well, but I could do a false sub and lot number would be easy to come up with.
With that all said is this structure appropriate or am I missing something as it progresses along to the invoicing tables ?
For primary keys, I suggest using a surrogate key (autonumber). The keys are ONLY for the system to maintain the relationships between data. You should not really be concerned about them. With the computing power available today, it really is no big deal to have an extra surrogate field which actually can be indexed and searched more efficiently than perhaps a natural key.
Let Access manage the keys. The primary key - autonumber (long integer) and the foreign key is the same field in the other table but set to long integer. I suggest using the SAME name for the foreign key as it makes it very easy to know what is the primary and foreign keys.
If I'm understanding properly the samples I've seen where some use the PK in the next table as the FK and others do not it's basically up to me to decide. To me it's a give me that this Lot is in this Sub and is owned by this Customer.
Table: Customers
CstID (PK)
CstName
CstAddress
Ext....
Table: Sub
SubID (PK)
SubName (FK) to CstID
Ext......
Table: Lots
LotID (PK)
LotNum (FK) to SubID
EXT......
If I have this correct down the road when I get that far I'd like things to be all linked to where the Each Lot is owned by the Sub table and the Sub table is owned by the Customer Table ( if owned is the proper term or close ). For the record this is leading to invoicing and job tracking if I can make it that far.
I would like to be able to do a report or something by Customers total ( all Subs and lots ), Subs ( all lots to particular Customer or just the sub over all as there may be more than one customer in each Sub ), and lastly just the plain old lot total. Ooooops and the big number all customers,subs, and lots totaled. Almost forgot there may be instances where there is just a customer as well, but I could do a false sub and lot number would be easy to come up with.
With that all said is this structure appropriate or am I missing something as it progresses along to the invoicing tables ?