Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-15-2019, 01:48 AM   #1
jjatwork
Newly Registered User
 
Join Date: May 2019
Posts: 17
Thanks: 30
Thanked 0 Times in 0 Posts
jjatwork is on a distinguished road
Normalization - Qs about junction tables and booleans.

I've done a lot of reading on normalization, but I'm having a hard time grasping some of the concepts.
Especially how to use foreign keys and junction tables.

Let's say I have a DB where I keep track of bicycles. I have a table, 'tblBikeInfo' with a lot of fields containing info about each specific bike, which I'm trying to separate into multiple tables.

As it is, I have a string field named 'BikeType' in my table and I'm restricting the textbox input to 'Male', 'Female' or 'Unisex'. If I normalize the DB, I'll end up with 1 or 2 extra tables. Will that not demand more resources from the server?
I don't mind putting in the work to do it right, I just want to be sure which path is the correct one before starting.

1. Properties with n:1 relations, like brand, size or, ie., color.
Is it best practice to make another table with colorID and colorName, keep the field in 'tblBikeInfo'and have the colorID as a FK, or would you remove the field from 'tblBikeInfo' and make a junction table, containing the bikeID and the colorID

Can anyone provide a brief overview of the differences between the two methods and when to use which one?


2. What about booleans, like a checkbox indicating if the bike is electric or not.
Is there any need for separating such properties into separate tables?


Thank you all in advance for any advice, tips and/or helpful comments.
JJ

jjatwork is offline   Reply With Quote
Old 05-15-2019, 01:55 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,329
Thanks: 156
Thanked 1,696 Times in 1,667 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Normalization - Qs about junction tables and booleans.

I'm assuming any bike can be many colours. Therefore to store the available colours for any given bike you would need a junction table storing the BikeID and the ColourID as Foreign keys. I would make them a unique combined PK so you can't list the same bike and colour twice by mistake.

However a brand is a fixed single entity for that BikeID's manufacturer, so you would have a Manufactures table, and store the Manufacturers ID in the Bike table as a Foreign key.

Multple normalised tables don't cause any significant overhead and generally simplify writing queries and reports.

Some people argue against tables with ID's for simple lookup values (Male/ Female is an obvious one).
However your Bike types may get additions (unicycle for instance) and it's much easier to add a record to the table, than to find all uses of the combo and add it manually to the list items.

I prefer storing an ID over the unique text value, it's cheaper resource wise, as if you mistype something correcting the source data corrects all the underlying displayed values automatically.

Does that help ?
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Minty; 05-15-2019 at 02:08 AM.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
jjatwork (05-15-2019)
Old 05-15-2019, 02:45 AM   #3
jjatwork
Newly Registered User
 
Join Date: May 2019
Posts: 17
Thanks: 30
Thanked 0 Times in 0 Posts
jjatwork is on a distinguished road
Re: Normalization - Qs about junction tables and booleans.

It certainly helps, thank you.

So, if I understand correct, in case of a n:1 relations, I would keep the field in the 'primary' table to hold a FK, whereas in the case of n:n I would make a junction table.
Or am I trying to oversimplify it?

I'm not sure what a lookup value is, but the reason you mention is exactly why I was leaning towards separating things like 'type' into another table. I'm thinking: 'If any addition to the choices is ever possible, it needs a separate table'.

Still not sure about booleans, though. It will always be true or false and can never have additional choices.
Is it ok to store such a property in my 'main' table?

Tyvm for you time and advice. It is very much appreciated.
JJ

jjatwork is offline   Reply With Quote
Old 05-15-2019, 03:18 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,329
Thanks: 156
Thanked 1,696 Times in 1,667 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Normalization - Qs about junction tables and booleans.

Quote:
So, if I understand correct, in case of a n:1 relations, I would keep the field in the 'primary' table to hold a FK, whereas in the case of n:n I would make a junction table.
Or am I trying to oversimplify it?
Nope you are spot on with the above.
Quote:
I'm not sure what a lookup value is, but the reason you mention is exactly why I was leaning towards separating things like 'type' into another table. I'm thinking: 'If any addition to the choices is ever possible, it needs a separate table
Again pretty good description of normalisation. The word commonly used is an Entity. and Entity type. Rather than having 3 fields called Male,Female & Unisex, as boolean tick boxes (Spreadsheet stylee), you correctly have a type and store that instead. When Unicycle arrives or Tandem, you don't have to redesign your whole database.

A lookup value is a table of related information that you use to control data entry. E.g. tblManufacturers
ManuID - PK Autonumber
Manufacturer - Text
other fields

You would use this whenever you wanted to store a Manufacturer in a related table e.g. tblBikes. This is to stop people typing in Eddie Merks, then Eddy Mearks instead of Eddy Merckx

You want consistent data, now you could just store the Manufacturer as a text field in the tblBikes but you really should store the ManuID as it's more efficient, and scales better.

With Booleans - simply store them as a Boolean field in the table. Yes/No True/False. definitely don't use a table for those. Some developers avoid booleans, and use a integer or byte field instead, I'll let you google research the arguments about that.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
jjatwork (05-15-2019)
Old 05-15-2019, 03:34 AM   #5
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,559
Thanks: 88
Thanked 1,477 Times in 1,394 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Normalization - Qs about junction tables and booleans.

You might not store Electric as a Boolean. Instead consider a Power field to record the Watts of the motor. Any value means it is electrics while Null means no motor.
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
jjatwork (05-15-2019)
Old 05-15-2019, 04:11 AM   #6
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,768
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Normalization - Qs about junction tables and booleans.

It's a bit tricky.


Let's say you a bike called a "Chris Boardman Special" that come in mulitple sizes, and a range of colours. How do you need to use your data. Do you want you user to

a) pick the bike, THEN
b) pick the size, THEN
c) pick a colour

or navigate to the bike in a different way.

If you have 3 of exactly the same bike, can you distinguish them. Do you have a serial number?

You need to consider all the atributes you might want, and then structure the data tables accordingly. Maybe you have a M boolean AND a F boolean, and you get Unisex by choosing bikes with both M and F set to true.

You don't get more tables because you have more variable attributes. You just have one bike table, but you filter the bikes based on the atrtributes you select.

It might be worth looking at the way on line Bike sites work, or maybe Car Sales sites work to see how they let you navigate to the model you want. Travel Agents are also good- how they let you drill down to the holiday you want. It's all the same really.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.

Last edited by gemma-the-husky; 05-15-2019 at 04:36 AM.
gemma-the-husky is offline   Reply With Quote
The Following User Says Thank You to gemma-the-husky For This Useful Post:
jjatwork (05-15-2019)
Old 05-15-2019, 04:14 AM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,564
Thanks: 38
Thanked 869 Times in 852 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Normalization - Qs about junction tables and booleans.

Hi. Perhaps I’m way off here but in most cases when the topics of boolean fields and normalization are discussed, it is usually about the issue of repeating groups. Meaning, if your table has multiple boolean fields, then it’s possible that they probably belong in a separate table.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
jjatwork (05-15-2019)
Old 05-15-2019, 05:51 AM   #8
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,073
Thanks: 81
Thanked 1,597 Times in 1,481 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Normalization - Qs about junction tables and booleans.

You said you have some confusion regarding use of foreign keys (FK) and junction tables.

The reason that normalization works so well is that there is a rule about how you place data in a given table. To have an FK, you first have to have a prime key (PK) in another table. Minty mentioned the concept of "entities" which is kind of generic sounding - because it has to be. Access and other databases tend to abstract the things (entities) they describe. So if your entity is a person, you would have name, birthday, gender, perhaps some physical descriptions like height & weight, and maybe a few other things.

With general databases, you never know ahead of time what tidbit of information you will need for some report or form, so how do you store all that info? By making a table dedicated to entities of that class - i.e. a person table. But now, you have records where that person must be identified. So you have two choices - the flat-file or spreadsheet way and the relational database way. In spreadsheets, you tend to repeat data. The relational way says to assign a unique PK to each record and make a copy of that PK (and ONLY the PK) in any table that needs to reference that person. In that referencing table, that copy becomes the FK and is the stand-in for any and all data about that entity (person). The FK becomes the pointer to where you find data about that person and allows you to design the correct database structures based on "knowing where to look" i.e. which table (the person table) and which record (indicated by the FK). The FK thus expresses the relation of the current record of interest to the person in question.

Crucial to this concept is "purity of entity." This is a rhetorical question: Where you do store data about a person? The answer is "It depends." In order to maintain purity of your table structure, there is a rule: Anything you store in a given table must be fully determined once you identify the prime key for that record. Anything that is NOT fully identified by the PK doesn't belong in the table. It belongs in some other table.

Case in point: Here is my record, sitting in a table somewhere and you want to ask two questions: What is my age and what car did I drive last year? You can compute my age from my birthday, so you go to the person table to look that up. But what about my car? Cars break down, or I get tired of them, or I get a wild hair somewhere. So which car I drove last year depends on my PK but it ALSO depends on my car ownership history which involves a DATE dependency. That second dependency means it cannot go in the person table because it violates the purity rule. It needs to be in another table that includes my person PK (technically, as an FK) and a couple of dates showing when I got the car and when I ditched it.

I'm going to break here and pick up with junction tables in another post.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
jjatwork (05-15-2019)
Old 05-15-2019, 06:27 AM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,073
Thanks: 81
Thanked 1,597 Times in 1,481 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Normalization - Qs about junction tables and booleans.

The other part of your question is about junction tables.

In Access you have three enforceable relationships: 1:1, 1:many, and many:1 - that's it. This leads to partner tables (1:1), child tables (1:many), or definitional tables (many:1). But the real world has a very common fourth relationship type, many:many, and it occurs a LOT.

The solution used by Access and most other DB packages that I know has to do with a middle-man table called a junction table. In logic theory, the junction table is used to enumerate or list the points of overlap between two other entities, each in their own tables. If you think of the overlapping circles of a Venn diagram, the individual tables are the circles and the junction table relates to the area where the two circles overlap.

Here's a case from my past when I was employed as a contractor for the US Navy. We had these "refresher courses" that we had to take each year to keep up our eligibility. Everybody had to take them and there were several to be taken. (Many/many!)

So to track that, the educational software had a person table that held every contractor using an identifier as PK. There was a course table that held every course on the required list (as well as some "optionals" for special-case certifications) using a course number as PK. The way to remember which contractor took which course was a junction table. In that table, you would have these values: My ID, the course ID, the mandatory completion date, and the date on which I completed the certification. There was also a "grade" field, which is important for illustrative purposes.

That junction established a pair of FKs - one to me, one to the course, and it also held some data that, for purity of entity reasons, didn't belong in either the person table or the course table. The date on which I took the course depended on my schedule and ability to take that course (which was on-line). It also depended on whether that course was required in a given year. Requirements changed from year to year as courses changed or Congress passed some new requirement or some admiral got a burr up his butt about what we needed to know.

It isn't always done this way because it depends on the nature of the requirement, but for this table, what they did was they kept the junction record of when I took the course but THEN at the beginning of the fiscal year, they did a sweep and loaded NEW junction records for each contractor and each required course, with an empty "taken" date and the last day of the new fiscal year as the due date. So the junction table was not only a requirements table but a history table as well.

Junction tables tend to have shorter records because they can refer to the joined entities through their FKs, but they CAN have data other than FKs. The example I gave was used for history as well as for requirements. It also recorded the score on the exam that was part of the course.

I mention the grade and "taken" date to show that junction tables have purity of entity as well. In this case, the entity is a bit abstract - an online session with a computer-aided learning course. But the entity showed things that were defined by the single record: student, course, due date, taken date, grade.

Interestingly enough, the "session" record might not have had a "true" PK, not that it matters. The purity of entity concept merely says that if there is a PK, then everything in the record must depend on it. If there is no PK, the record contents should still depend on what it would have been if you had one. And it is not uncommon for a junction table to NOT have individual PKs. OK, in the Navy case, a compound PK was possible - person ID, course ID, and Due Date. But there is no way for me to know if that was an actual compound PK or merely a viable candidate for selection as PK.

This entity record is ALSO subject to the "purity" concept because it is a table in a relational database. And because it has two FKs (one to each of two tables), it has two parent tables.

The nomenclature for relationships talks about parent/child entities. Another way of saying this is "dependent" or "independent" entities. A person is an independent entity. A particular course is an independent entity. The session where a person takes a course is a dependent entity. In relational terms, the independent entities have PKs whereas the dependent enties will have FKs to the things on which they depend. So the junction table represents a doubly-dependent entity.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
jjatwork (05-15-2019)
Old 05-15-2019, 09:02 AM   #10
jjatwork
Newly Registered User
 
Join Date: May 2019
Posts: 17
Thanks: 30
Thanked 0 Times in 0 Posts
jjatwork is on a distinguished road
Re: Normalization - Qs about junction tables and booleans.

Quote:
Originally Posted by Minty View Post
Nope you are spot on with the above.


Again pretty good description of normalisation. The word commonly used is an Entity. and Entity type. Rather than having 3 fields called Male,Female & Unisex, as boolean tick boxes (Spreadsheet stylee), you correctly have a type and store that instead. When Unicycle arrives or Tandem, you don't have to redesign your whole database.

A lookup value is a table of related information that you use to control data entry. E.g. tblManufacturers
ManuID - PK Autonumber
Manufacturer - Text
other fields

You would use this whenever you wanted to store a Manufacturer in a related table e.g. tblBikes. This is to stop people typing in Eddie Merks, then Eddy Mearks instead of Eddy Merckx

You want consistent data, now you could just store the Manufacturer as a text field in the tblBikes but you really should store the ManuID as it's more efficient, and scales better.

With Booleans - simply store them as a Boolean field in the table. Yes/No True/False. definitely don't use a table for those. Some developers avoid booleans, and use a integer or byte field instead, I'll let you google research the arguments about that.

Thank you for the confirmation and the in-depth explanation. It certainly helps me understand the issue!

I've done some reading and now better understand what entities and lookup values are.
Just a quick question, though. In this case, the colour would still be an attribute of the entity (the bike), even though it is separated into its own table, correct?
Or would the 'ColourID' in theory be the entity in its own table and the 'ColourName' the attribute?

I'v been working under the assumption that using a meaningless PK comes with more benifits than not, so for most of my tables the PK is simply an auto-increment.

For now, I will keep booleans in the 'main' table, but I'll keep in mind that I need to do some thorough research on the subject.

Thank you again for taking the time.

Quote:
Originally Posted by Galaxiom View Post
You might not store Electric as a Boolean. Instead consider a Power field to record the Watts of the motor. Any value means it is electrics while Null means no motor.

Of course! I hadn't even thought about that.
I think I've read many times, that non-PK fields should not be inter-dependent, but sometimes it helps to get the same info in a different way.
Since I need to track which battery is installed on the bikes anyway, I guess I could apply your example to that entry. (Entity?)

Brilliant. Tyvm for your comment.

Quote:
Originally Posted by gemma-the-husky View Post
It's a bit tricky.


Let's say you a bike called a "Chris Boardman Special" that come in mulitple sizes, and a range of colours. How do you need to use your data. Do you want you user to

a) pick the bike, THEN
b) pick the size, THEN
c) pick a colour

or navigate to the bike in a different way.

If you have 3 of exactly the same bike, can you distinguish them. Do you have a serial number?

You need to consider all the atributes you might want, and then structure the data tables accordingly. Maybe you have a M boolean AND a F boolean, and you get Unisex by choosing bikes with both M and F set to true.

You don't get more tables because you have more variable attributes. You just have one bike table, but you filter the bikes based on the atrtributes you select.

It might be worth looking at the way on line Bike sites work, or maybe Car Sales sites work to see how they let you navigate to the model you want. Travel Agents are also good- how they let you drill down to the holiday you want. It's all the same really.
It's very tricky. At least for me.

The DB is for internal use. Users will be keeping track of a number of bikes and the repairs being done on them. (Maybe I should add this info to my profile or signature? I want to be up front about seeking help in a real-case project, to secure steady employment, so some may not consider it non-profit.)

I'm not entirely sure how I built the lookup function, I'll have to double-check.
Some bikes share BikeID in the current system and physically, so I need to negotiate that issue.
Thankfully, the bikes have serial numbers and I've given each bike an internal DB_ID which is specific to each one.
Come to think of it, though, in some queries and functions I might actually have passed the BikeID as an argument instead of the DB_ID, which of course will result in some errors down the line.
I'll have to look at that.

Thank you for your input. Keeps the wheels spinning.

Quote:
Originally Posted by theDBguy View Post
Hi. Perhaps Iím way off here but in most cases when the topics of boolean fields and normalization are discussed, it is usually about the issue of repeating groups. Meaning, if your table has multiple boolean fields, then itís possible that they probably belong in a separate table.
As of right now, I have 3 fields containing booleans, indicating if the bike is electric, in-house and if payments are being made.
Going by Galaxiom's advice, I should be able to deduce if it's electric and/or in-house, by looking at related information, so some fields in the table was indeed redundant.

Thanks for your comment, any input is appreciated!


@ Doc

Thank you! I will have a read after dinner.
jjatwork is offline   Reply With Quote
Old 05-15-2019, 10:03 AM   #11
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,329
Thanks: 156
Thanked 1,696 Times in 1,667 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Normalization - Qs about junction tables and booleans.

If you ares simply storing repair details then a brand, type, serial number, colour would probably suffice.

Only record (e.g. have to enter) the data you need to achieve your task. Recording where the saddle height was set and what the customer had for breakfast probably don't actually do anything for you even if nice to have. Brevity can be a good idea.

If you are also selling then frame sizes, gear assembly, and a whole heap of other shizzle arrives.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
jjatwork (05-15-2019)
Old 05-15-2019, 10:19 AM   #12
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,768
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Normalization - Qs about junction tables and booleans.

If its for a repair shop, I can't see the relevance of colour, personally - unless you need to paint it. Is it just for ID.


In this case, I would add whatever description you want to identify the bike. I doubt the bike name matters so much. Attach a physical label with a unique reference number, customer name maybe, and so on. Put this information in your bike table. Maybe take a few photos, and store these as jpegs attached to the bike ID.

You will know whats important. Maybe the chainset details/pedal types/brake info is more important than the bike itself.

It's a bit like a car repair shop, I imagine. You need a unqiue registration number to identify the motor, and a work sheet to decide what you are going to do. Then you need to record parts used, and labour hours. Something like that?
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
The Following User Says Thank You to gemma-the-husky For This Useful Post:
jjatwork (05-15-2019)
Old 05-15-2019, 12:24 PM   #13
jjatwork
Newly Registered User
 
Join Date: May 2019
Posts: 17
Thanks: 30
Thanked 0 Times in 0 Posts
jjatwork is on a distinguished road
Re: Normalization - Qs about junction tables and booleans.

@ Doc

Thanks for the detailed walkthrough.

I've already separated Entities like Dept. and Team, which has lots of additional info like address, phonenumber etc. into their own tables, but I was unsure how to deal with things like colour and size, which will only have a few fields in their table.

I'll try to apply the 'purity of entity' concept and sort out any inter-dependencies.

I've been through relations a lot and have looked at those overlapping circles you speak of, but it simply does not compute for my brain. I'm sure it will sink in at some point.
I've seen the 'classes taken' example before, but again it's a case of being presented the same information in a different way. Your explanation made me realize a key component of junction tables: Sometimes they will contain data about the specific combination of FK's, which cannot be appropriately contained in any of the (parent?) tables they refer to. Is this correctly understood?

You lost me a bit at the end with the 'junction tables don't always have PK's'. I was under the impression that a juntion table always had a compound PK.
I think I have to start out as simple as possible and cross that river when I get there.

I really appreciate that you took the time for such a detailed explanation. Tyvm!



@ Minty and Dave

Nothing is being sold.
I'm not sure how much I'm allowed to disclose, but it's more like a servicecenter, which occasionally lends its bikes out of house, that has an inhouse repairshop.
So the DB is supposed to keep track of repairs made, which department, group and team the bike is currently designated to, buydate, milage, supplier and some other information for insurance/warranty purposes and some additional info for billing.

Dave. Yes, the colour is 'needed' to make it easy to visually identify the bike physically.
Your example with the car repair is about right. The thing is that I have to make 2 different 'systems' to keep track of the repairs. All repairs on inhouse bikes only need a brief note on what has been repaired, date and name of the mechanic, while all repairs on bikes designated to external users must be more detailed for billing purposes.

Maybe it's more complex than I anticipated at first, but I'll figure it out.

Thank you for both your comments.

Last edited by jjatwork; 05-15-2019 at 12:32 PM. Reason: spelling
jjatwork is offline   Reply With Quote
Old 05-15-2019, 05:59 PM   #14
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,073
Thanks: 81
Thanked 1,597 Times in 1,481 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Normalization - Qs about junction tables and booleans.

Quote:
Sometimes they will contain data about the specific combination of FK's, which cannot be appropriately contained in any of the (parent?) tables they refer to. Is this correctly understood?
Correct. For example, in my training requirements table, the grade isn't purely an attribute of the person and isn't purely an attribute of the class. It is an attribute of whatever the junction represents, which in this case might be called a "session." This grade's existence depends on TWO tables - the person and the class - plus the due date in the junction record. So based on purity, the only place the grade CAN go is in the place that holds all the things it depends on.

Further note: Sometimes it is a real bear to try to figure out what the junction represents. Here, it is a session. But some junction tables are so abstract in nature as to defy immediate description. That is, however, not due to Access, but due to the abstract nature of the problem being analyzed.

Quote:
You lost me a bit at the end with the 'junction tables don't always have PK's'. I was under the impression that a juntion table always had a compound PK.
Technically, NO table needs a PK if nothing depends on it. (I.e., more specifically, it is not a parent / has no child records.) Stated another way, if you have no need of an FK pointing into that table, you do not need a PK. However, depending on some SQL implementations such as SQL Server, there MIGHT be a rule that requires a PK because of the specifics of that version of SQL. I believe SQL Server requires a PK on any table you want to be updatable - but that isn't a requirement of "pure" SQL set theory or ANSI standard SQL.

Here is the key distinction that might have confused you. Access native tables with no dependents NEVER need a PK for relation management. HOWEVER, they might still need a PK (probably compound) or multiple independent indexes for search optimization. A PK is an index - but an index doesn't have to be a PK.

In the class table I showed you from my Navy days, there MIGHT have been a PK but it was most definitely not obvious to us. But there was a perfect candidate for a compound key based on {person, class, due-date} as a "natural" PK. Whether that combination was a true PK or the table merely had an index on each field for searching is open to question since it surely wasn't an Access table and we didn't get to see the design view.

The junction certainly did NOT need a synthetic PK (autonumber or some other computed key) because nobody would ever look it up based on the synthetic key. They would look it up based on indexes of the three fields that were members of the so-called candidate key. As long as the individual indexes existed, it didn't matter whether there was a separately declared PK.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.

Last edited by The_Doc_Man; 05-15-2019 at 06:08 PM.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
jjatwork (05-20-2019)
Old 05-16-2019, 02:46 AM   #15
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,768
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Normalization - Qs about junction tables and booleans.

Quote:
Originally Posted by jjatwork View Post

partial extract

@ Minty and Dave

Nothing is being sold.
I'm not sure how much I'm allowed to disclose, but it's more like a servicecenter, which occasionally lends its bikes out of house, that has an inhouse repairshop.
So the DB is supposed to keep track of repairs made, which department, group and team the bike is currently designated to, buydate, milage, supplier and some other information for insurance/warranty purposes and some additional info for billing.

Dave. Yes, the colour is 'needed' to make it easy to visually identify the bike physically.
Your example with the car repair is about right. The thing is that I have to make 2 different 'systems' to keep track of the repairs. All repairs on inhouse bikes only need a brief note on what has been repaired, date and name of the mechanic, while all repairs on bikes designated to external users must be more detailed for billing purposes.

Maybe it's more complex than I anticipated at first, but I'll figure it out.

Thank you for both your comments.
I am pretty sure you can deal with both internal and external repairs with a single "repairs" table. The similarities must outweigh the differences. All you would need is a flag in the table to distinguish the 2.

The colour can be approximate then, I take it. Just "red" rather than a specific paint shade.

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
The Following User Says Thank You to gemma-the-husky For This Useful Post:
jjatwork (05-20-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Forms that update/create records in multiple tables, including junction tables excal Forms 5 07-11-2011 08:46 PM
Junction Tables lmg0115 Tables 1 08-20-2008 06:48 AM
Normalization: One-to-many out of a junction table without a primary key rbrady Tables 8 04-08-2008 01:16 PM
Junction Tables patkeaveney Tables 6 10-13-2005 06:54 PM
Junction Tables munday63 Tables 1 03-02-2005 08:40 AM




All times are GMT -8. The time now is 01:35 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World