PK and FK relationships? (1 Viewer)

[boophoenix]

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.

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 ?

 

[vbaInet]

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 ?

I would imagine it this way:


Table: Customers
CstID (PK)
CstName
CstAddress
Ext....

Table: Sub
SubID (PK)
CustID_FK (FK) linked to CstID in Customers table
Ext......

Table: Lots
LotID (PK)
SubID_FK (FK) linked to SubID in Sub table
EXT......

Does this help?

 

[Banana]

boophoenix-

The important thing here is that we need to clearly define the terms as while we may have an idea of what you may mean by 'sub' and 'lots', it may not line up with how you actually use those terms.

To provide an example, you said:

there may be more than one customer in each Sub

If a single Sub could have more than one customers, and a customer can have more than one sub, then you actually have a many-many relationship and will need one more table to represent the junction between customer table and sub tables.

vbaINet already pointed out that you need a column "CustID_FK" to associate with the "CstID" rather than original SubName. The foreign key must be of same data type, which means that if you use a integer for primary key, the foreign key should be an integer as well. One common stumbling blocks is that if primary key is defined as an autonumber, they may try to define foreign key likewise... you don't. Just use Long Integer (that's "Number" then on the bottom of table design view, "Long Integer" as a sub-type). You still can keep the SubName as a separate column.

Did that help?

 

[gemma-the-husky]

one reason for BobLarson suggesting using an autonumber PK is the following

------------
lets say we have tables for customers and orders, where 1 customer can have several orders - then (to keep it simple with just 2 tables) you have

customer table
custname
custaddress
custphone
etc

orders table
orderno
cust_FK
ordervalue
orderdate
etc


now the thing is that the customer identifier in the order table, needs to have the same value as the customer name in the customers table for the relationship to work. (the red fields must correspond)

for a number of reasons it is useful for the indexed value in the customers table to be a number rather than a text string, so this implies that we have an additional numberic customerid in the customers table - and store this value in the orders table, instead of the customer name - and generally we make this an autonumber

principally
numbers are more efficient for access to use than text

but practically
- it means that the customer name can be changed, if neccessary in the customers table - without it affecting the orders table at all
- the user doesnt have to even see the autonumber value, and doesnt need to decide what it should be when entering a new customer - access does this behind the scenes


so the argument comes down to whether these benefits are worth having an EXTRA key in the customers table

since you will now need
a) the autonumber key AND
b) the name key as well - since the name should still be keyed, to ensure you can sort and search the name field efficiently, and avoid duplicates

and the consensus is that it is definitely worth doing so.

 

[boophoenix]

boophoenix-




If a single Sub could have more than one customers, and a customer can have more than one sub, then you actually have a many-many relationship and will need one more table to represent the junction between customer table and sub tables.

vbaINet already pointed out that you need a column "CustID_FK" to associate with the "CstID" rather than original SubName. The foreign key must be of same data type, which means that if you use a integer for primary key, the foreign key should be an integer as well. One common stumbling blocks is that if primary key is defined as an autonumber, they may try to define foreign key likewise... you don't. Just use Long Integer (that's "Number" then on the bottom of table design view, "Long Integer" as a sub-type). You still can keep the SubName as a separate column.

Did that help?

Okay I'm getting it now the high lighted part above makes since in that they should be of the same data type.

The table structure is still up in the air in a way. Not being certain of the relationship structure I backed off. Until I figured out a little more about the relationship issue it wouldn't matter if I had 2 tables or 500.

A little in site on the tables listed. It's construction based with the customer being the builder ( Bobs construction company ), sub being a subdivision ( access acres subdivision ) , and lot being the individual home sites .

So the end game will hopefully be a little simpler way of inputing data for the invoice and printing it with a lot more info in reporting. Invoice 100001 will be to bobs construction company Ect... , at Access Acres, Lot number 100

I'm thinking it's all a one to many, but may be wrong. One builder to one or many subdivisions, one subdivision to one or many lots. AT the same time Banana construction, gemma-the-husky construction, or vbaInet construction may also have some lots in Access Acres. Or does the previous post of considering a report by subdivisions require a many to many on sub and customer?

Also is there any reason there would be a problem if vbaInet construction decided to do a single job not in a subdivision say just on 101 VBA road which could just translate to ? Looking like VBA road would have to become my subdivision as 101 could be used many times in lots and subs table is what would make it unique?

 

[Banana]

Okay I'm getting it now the high lighted part above makes since in that they should be of the same data type.

Just to be sure this wasn't overlooked - it shouldn't mean you should use autonumber for foreign key because that was what you used for primary key. Autonumber is just a special kind of Long Integer, but you want plain old Long Integer for foreign key.

The table structure is still up in the air in a way. Not being certain of the relationship structure I backed off. Until I figured out a little more about the relationship issue it wouldn't matter if I had 2 tables or 500.

Well, I think you're now realizing how important it is to clearly define the entities you need to track and how they relate to each other. Your database's success will certainly depend a lot on how it is designed.

I'm thinking it's all a one to many, but may be wrong. One builder to one or many subdivisions, one subdivision to one or many lots. AT the same time Banana construction, gemma-the-husky construction, or vbaInet construction may also have some lots in Access Acres. Or does the previous post of considering a report by subdivisions require a many to many on sub and customer?

Well, again, what is a 'builder'? For example, I can imagine a plumber, an electrician, and carpenter all working on same lot. They may not be from the same company and thus you get a separate invoices for each. If this is the case, then you do have a many-many relationship. OTOH, if you don't deal with this kind of stuff and just have a general contractor so that you always have single builder on a lot (even if they subcontract others but it doesn't change the fact you only get invoice from one builder). Also, be sure to think of future -- maybe right now you only take in invoices from general contractors, but was there a special situation or could your business expand so that you end up needing an invoice from a different company for the same site?

Also is there any reason there would be a problem if vbaInet construction decided to do a single job not in a subdivision say just on 101 VBA road which could just translate to ? Looking like VBA road would have to become my subdivision as 101 could be used many times in lots and subs table is what would make it unique?

What I would suggest is that you don't work in Subdivisions & Lots but rather in Sites, then provide a description of what the site is... a housing lot, a road, etc.. That way, you have the freedom to describe what a site is without it being a lot even if that is the case for 98% of your invoices.


Did that help?

 

[boophoenix]

Thank ya for the help banana i picked up on the auto number to long integer.

I've got to hit the work stuff for a bit and go to as meeting, but will be back a a little while.

Thank ya for the help everyone. Good forums are a rare find.

 

[boophoenix]

Well, again, what is a 'builder'? For example, I can imagine a plumber, an electrician, and carpenter all working on same lot. They may not be from the same company and thus you get a separate invoices for each. If this is the case, then you do have a many-many relationship. OTOH, if you don't deal with this kind of stuff and just have a general contractor so that you always have single builder on a lot (even if they subcontract others but it doesn't change the fact you only get invoice from one builder). Also, be sure to think of future -- maybe right now you only take in invoices from general contractors, but was there a special situation or could your business expand so that you end up needing an invoice from a different company for the same site?

I am a sub contractor in this instance and only need to bill the general contractor per lot. Might bill in stages on occasions but individual invoices would solve that problem I think. Then lot reports would just search for all pertaining invoices on that particular lot.

What I would suggest is that you don't work in Subdivisions & Lots but rather in Sites, then provide a description of what the site is... a housing lot, a road, etc.. That way, you have the freedom to describe what a site is without it being a lot even if that is the case for 98% of your invoices.


Did that help?

You were close with 98% it's about 99.9%. The rare instance it's not a subdivision lot there are easy ways to describe the job based on subdivision and lots. Example location and job number if commercial work, street and street number for residential . So I don't see that being an issue at present or down the road unless I'm over looking something. It also still lets me break down what was earned on that exact job per phase / lot. From time to time commercial work is broken up into phases or lots as well so still falls into the same categories. If I had to I could always add a catch all subdivision category for random work and assign a number for lot.

If there is some reason why this isn't a good option I'm not seeing it and would be grateful for any reasoning as to why? The end result of my way is the lot amount is the key number from the PO's. Normally each builders subdivision pays X amount ( or flat rate ) for this aspect of work. If it takes an hour or 10 hours Which in the long run balances out to some degree. One piece of equipment or three isn't that important till ya get to the point of the VPO.

The way most contractors at the moment pay is by the lot for work preformed that they estimated prior to the start of construction. Almost all of it is done on a PO system from them and my end has to match ( when, lose, or draw unless there is an exception for a VPO, but not always ). In which case it would be very handy to be able to pull a report by lot to see if the numbers are balancing for time and equipment used per lot.

Ideally it would be nice to bill by equipment, although that's not how it is done in most cases. So some of the numbers will always be left up to figuring by hand or by an average. With housing the way it is right now the numbers are very important as most have cut pay rates to maintain there profits. At the same time we've had to expand at the cost of more time on the road between subdivisions ( doesn't pay anything ). Two years ago it was one builder with 8 subdivisions, six of which all connected. Didn't even have to run tags on the trucks there as they never actually hit public roads so that's an almost 2k loss in tags and heavy highway taxes now along with rate cuts. Then toss in fuel costs on top at 6 MPG with some of the subdivisions being 70 to 90 miles apart. I think I average about 75 gallons a working day in fuel which went up $0.12 a gallon in one day last week.

At the moment there are three main builders ( customers ), 1st one having ruffly 8 subdivisions, 2nd having 6 subdivisions, 3rd having 5 subdivisions. Along with a couple of smaller builders and a few habitat houses.

Out of these we mainly do 5 to 10 aspects of the job per lot ( these are the PO items ). Prices can vary by subdivision with the same builder because of circumstances. Also 12 to 20 pieces of equipment ( these can be VPO items ) may be used on various lots.

The reasoning behind building this ap is I don't know of anything out of the box to serve the purpose I want. I enjoy tinkering with new things like this from time to time, but have a lot of other things I really need to be devoting time to right now that will earn me income. How ever this will progress slowly as the report aspects if I can get them right will be the same as earning a little in the long run I think even if it just gives more free time to work more instead of crunching numbers. The paper work side of the business doesn't pay anything it just gets you payed ( most of the time ). So accuracy and then speed are of key values.

 

[boophoenix]

A hypothetical way to look at my idea:

Say I have a huge electronics manufacturing company building all kinds of items for multiple companies.

customers ( aka builder for me ) would be Sony, HP, ect...

product type ( aka subdivision ) computers, cameras, ect....

Models ( aka lots ) NVR-23, ect... ( this one isn't falling in line real well, but bet some where model numbers have crossed before )

I'd be willing to bet that a lot of the parts in these items are the same or close enough for arguments sake ( like maybe the video card in my wonderful NVR-23 I once had or better yet some of the resistors or diodes in these items should be common across all products and very like manufactures as well). This would relate to the work preformed in my case. Most of the work is the same thing just a different place.

Maybe this is where the trouble is. I haven't gotten to the work rates, invoice, invoice lines tables for the invoicing stuff yet. I didn't see the point yet till I get this structure for who and for where I'm billing figured out. Then the what I'm billing for will come into play.

And since The customer / builder is repetitive along with the subdivision I wanted them in there own tables ( also thought this was kind of the correct way and reasoning for databases ). The lots are all unique but only if linked to a subdivision as the numbers have a tendency to start around 101 and go up in larger subdivisions. The lot numbers can also skip upwards from say 150 to 200 ( for phase two and so on ). Just a note I am aware there are instances the builder name or address may change so this is one edit for all relating new info as the old needs to stay the same. The subdivision will always be the same or at least as long as it is relevant to me any way.

I could get rid of subdivisions table by adding things to the lot numbers example AA101 ( Access Acres 101 ) and still get all the functionality I want. But then I would need to manually enter the subdivision name to the invoice for the builder in case they didn't pick up on the AA. Adding the subdivision to the builders table seems wrong as the builder would be listed over and over depending on the number of subdivisions not to mention the chance of error in selecting the wrong builder from a list ( have a builder with a subdivision named belvista, and a belshire getting in a rush these could easily be mixed up if they were in the builder table I would think.

The builder info is to show who I'm billing for my info mostly as with most invoices ( not very often do you send the wrong invoice to the wrong person ). The subdivision is for both of our benefits as Bobs const company may have 8 or 10 lot 100's, but the subdivision will only have one lot 100. When I get further along into the invoicing parts there will be as many rough ins. finish grades, ect as there are lots in the subdivision.

If I'm off in this assumption I would be grateful for some reasoning as to why or a link to some info to explain why so I could try to figure it out.

 

[Banana]

Hi- wanted to say I'm glad to see you putting in a lot of thoughts.

I am a sub contractor in this instance and only need to bill the general contractor per lot.

Okay, so you're building an invoice database to maintain records of all work you do on a site for billing? Your description also suggests that ultimately it's to help you keep on top of accounting and avoid overruns because you're being paid a flat rate, so you need to know how much hours you can afford on a job before you sink into red as well?

You were close with 98% it's about 99.9%. The rare instance it's not a subdivision lot there are easy ways to describe the job based on subdivision and lots. Example location and job number if commercial work, street and street number for residential . So I don't see that being an issue at present or down the road unless I'm over looking something. It also still lets me break down what was earned on that exact job per phase / lot. From time to time commercial work is broken up into phases or lots as well so still falls into the same categories. If I had to I could always add a catch all subdivision category for random work and assign a number for lot.

Well, you could use a catch-all subdivision category but what if a time come that you need to drill down on those? Add more categories? You will have to be prepared for the trade off of extra maintenance should your future requirement expand into those and that 0.01% work becomes 1% with 100s of invoices that need to be classified properly. If you don't expect this to change, then by all means, keep it simple and use a special subdivision with an invalid number (e.g. 0000?) to mark such invoices done on those locations.

If there is some reason why this isn't a good option I'm not seeing it and would be grateful for any reasoning as to why?

Well, to provide an example - if you needed to examine your invoices for all work you did on say, electrical wires on road, but it's all mixed up with other invoices where you did say, sewer work on the road or concreting on the road. You could search for 'electrical' but maybe someone didn't actually put it down, or misspelled 'electrical'? Too easy to miss out on those invoices, so it could be problematic to maintain grouping if it's dumped in a catch-all subdivision. Of course, it could be partially solved by making another catch-subdvision but for electrical work, other for sewer but now you have to keep track of what is actually a subdivision and what isn't.

If those won't ever apply to you, then you don't have to worry about this, but that is a topic you need to keep in mind for the future when your business expands or changes.

The reasoning behind building this ap is I don't know of anything out of the box to serve the purpose I want. I enjoy tinkering with new things like this from time to time, but have a lot of other things I really need to be devoting time to right now that will earn me income. How ever this will progress slowly as the report aspects if I can get them right will be the same as earning a little in the long run I think even if it just gives more free time to work more instead of crunching numbers. The paper work side of the business doesn't pay anything it just gets you payed ( most of the time ). So accuracy and then speed are of key values.

Quite understandable. One possible idea is to see if you can locate a template that you can use to base your application.

http://www.databaseanswers.org/data_models/index.htm

A hypothetical way to look at my idea:

...

If I'm off in this assumption I would be grateful for some reasoning as to why or a link to some info to explain why so I could try to figure it out.

Well, if we're sticking with the earlier proposed 'Sites' to keep a level of generality, I suppose we could design in such way that you can define what kind of site you want from a list, and choose "Lot" (and have it be the default as well). Selecting a lot shoudl then automatically select the builder & subdivision because when you add a new lot in the database, you already define those things so you only need to define a lot once, and your invoices refer to lots. IOW, it's usually easier to decide on the lowest level of granularity and have your invoices refer to this level. You still can group by subdivisons, or by builders.

Example:

tblBuilders
BuilderID
BuilderName
...

tblSubDivisions
SubID
SubName
...

tblLots
LotID
LotName
BuilderID <-- foreign key to the builder table
SubID <-- foreign key to SubDivisions
...

tblInvoices
InvoiceID
LotID <-- refers a lot... we know who builder & subdivision via the link.
...

Did that help?

 

[boophoenix]

Hi- wanted to say I'm glad to see you putting in a lot of thoughts.

Thank you. I figure if I'm gonna do it might as well try to do it right. I don't want to get started wrong and be way off down the road having to come back to the beginning because I was wrong out of acceptance.

Okay, so you're building an invoice database to maintain records of all work you do on a site for billing? Your description also suggests that ultimately it's to help you keep on top of accounting and avoid overruns because you're being paid a flat rate, so you need to know how much hours you can afford on a job before you sink into red as well?

Simply put this is correct. In the end it will also be used for bidding new work as I hope to have a better track of things.

Well, you could use a catch-all subdivision category but what if a time come that you need to drill down on those? Add more categories? You will have to be prepared for the trade off of extra maintenance should your future requirement expand into those and that 0.01% work becomes 1% with 100s of invoices that need to be classified properly. If you don't expect this to change, then by all means, keep it simple and use a special subdivision with an invalid number (e.g. 0000?) to mark such invoices done on those locations.

We're not quite on the same page in this part I do not think, but I honestly believe my idea of the starting tables structure is correct for my situation and can cover the 1% if entered correctly. I'm reasonably sure I'm going in the right direction with my plans.

Well, to provide an example - if you needed to examine your invoices for all work you did on say, electrical wires on road, but it's all mixed up with other invoices where you did say, sewer work on the road or concreting on the road. You could search for 'electrical' but maybe someone didn't actually put it down, or misspelled 'electrical'? Too easy to miss out on those invoices, so it could be problematic to maintain grouping if it's dumped in a catch-all subdivision. Of course, it could be partially solved by making another catch-subdvision but for electrical work, other for sewer but now you have to keep track of what is actually a subdivision and what isn't.

If those won't ever apply to you, then you don't have to worry about this, but that is a topic you need to keep in mind for the future when your business expands or changes.

This is covered I think in future tables. I plan to do a table by the type of work preformed or equipment used that will auto fill the prices for such based off of who the builder is and/or what subdivision it is ( the major time saver if these prices are static which is 90%, also eliminates data entry errors ). If this is a bit vague I apologies, but I have a plan there.

Quite understandable. One possible idea is to see if you can locate a template that you can use to base your application.

http://www.databaseanswers.org/data_models/index.htm

Wonderful link. I haven't found that one yet in my searchings. I saw one in a quick browse threw just now that is close to my idea number 115 in the list. Also at this stage is kind of exactly what I was looking for. I'm not wanting to just try to redesign someone else's project to fit my needs. I want to design my own to do what I want and in that process I'll know why each thing does what it does should there be a problem later on.

Well, if we're sticking with the earlier proposed 'Sites' to keep a level of generality, I suppose we could design in such way that you can define what kind of site you want from a list, and choose "Lot" (and have it be the default as well). Selecting a lot shoudl then automatically select the builder & subdivision because when you add a new lot in the database, you already define those things so you only need to define a lot once, and your invoices refer to lots. IOW, it's usually easier to decide on the lowest level of granularity and have your invoices refer to this level. You still can group by subdivisons, or by builders.

Example:

tblBuilders
BuilderID
BuilderName
...

tblSubDivisions
SubID
SubName
...

tblLots
LotID
LotName
BuilderID <-- foreign key to the builder table
SubID <-- foreign key to SubDivisions
...

tblInvoices
InvoiceID
LotID <-- refers a lot... we know who builder & subdivision via the link.
...

Did that help?

The sample tables you list are almost exactly what I had when The relationship thing hit me. I stalled out trying to figure out the why of the relationships. I wanted to understand why and just not accept that's how ya do it. Here's a pdf for the relationship report from what I was working on when I asked the original question. It's where I stood last week in my ideas the only two I still have up in the air I think are the LotTotal row I think it may be bad here ( as it will likely be covered in the invoive table ) and now that I look at it again the LotEnd could go away as well ( The last invoice on the lot could cover this ).

View attachment Report1.pdf

Thank you for the patience and efforts thus far. I hope as I gather more info I can figure these things out more on my own. The early stages of things always proves as complicated as the latter to me as the simplest things are rarely explained they are assumed a lot. I plan to try to pick up a couple of books for learning and reference in the next few days to hopeful solve some of my questions. Honestly I didn't even know what access was till about three weeks ago.