Relatively New to Access - Relationship Design

[Exxili]

Hello there,

Joined this website after receiving tons of help from here anonymously, However I have now hit a quite confusing part in my learning of Access. Relationship design. I am hoping that some wonderful person here will set me on the right path :banghead:

Basically I am using a copy of an Old Stock Database of electrical components for my learning process and I am trying to refine it. It had no relationships of any kind.

I have kind of been going through harder and harder relationship models to try and hammer in the theory however as they get more complex I find I am struggling to get my head around them at all. Any advice on how to actually get better at more complex models would be greatly appreciated, Right now i try to boil them down to the most basic values needed and go from there but for this particular model that seems to have failed me.

Regardless onto the issue.

Above is a visual representation of my thought process on this, each square there is a table and I need to tie them all together so that there is the following:

• One Master Part Number for Every Capacitor
• May be Distributors per MasterPartNumber (or ManufacturerPartNo??)
• Only one Manufacturer Per MasterPartNumber
• All of this will link together into a Capacitor Table allowing the creation of Capacitor Records

Right now this looks like a maze of Potential Relationships that I have played around with and got wrong around 5-10 times now. So I am hoping someone could actually help me get my head around this!

Thanks for your time regardless, I appreciate it.

 

[plog]

You've made a mistake in the right direction. By that I mean, you've over-normalized your data.Considering the alternative (no normalization) that is a good thing--you've read up on it and have an understanding of it.

From what I see you have too many tables. A rule I use, is that you shouldn't have a table with one real field (autonumbers don't count as real data). So all your Size and type fields probably should go. Instead of storing a foreign key to the table (e.g. CapTypeID), just store the value itself (Type) in the external table.

Also, ManufacturerPartNumbers, MasterPartNumber and Capacitors Table should have their data combined into one table. It seems they have a 1-1 relationship among them, which means seperate tables isn't necessary.

Lastly, avoid synonyms. You have a Distributors table, but all the fields in it are prefixed with 'Supplier_', I assume a Supplier is a Distributor. Looking in from the outside, I'm not sure because you've used 2 different terms. Pick 1 term and live with it everywhere--otherwise confusion creeps in.

 

[MarkK]

What is your purpose? What do you want the database to do? Without knowing that, we can't really say how to structure or relate anything. For instance, if this system is for buying parts we would not have the Manufacturer and the Distributor as equals in the data, since we will not buy from the manufacturer. The manufacturer data is simply an attribute that a part may have, AND our internal part number may be able to be supplied by different manufacturers. Maybe we have an internal part number for 3/4" plywood, but we can buy that product from Weyerhaeuser or Canfor. Maybe the distributor sells those as different part numbers, but that's what we need to know first. What are the rules that govern that data? That is what determines your table design and your relationships, and that is determined by your purpose.

Similarly, if we are creating a purchasing system we may not need to know the capacitor type or ceramic size as critical parts of our data system. Maybe these are specs we want to maintain with our parts, but strictly for purchasing and stock management, it doesn't matter.

Also, in a stock system, you do not store the "QuantityInStock", you always calculate it. Think of that number like a bank balance, in which is calculated in time as...

BalanceInTime = DepositsPriorToTime - WithdrawalsPriorToTime

...because in any stock system worth having, you want to be able to look at historical stock balances, and predict future ones given current orders.

So what is you purpose? Answer that question very, very specifically first. What data do you have to enter? What data should the system produce?

Hope this helps,

 

[Steve R.]

Plog and MarkK have provided good advice. My additional thoughts on this is that the "Capacitors Table" is the "central (main) table". Other tables such as "manufacturers" and "distributors" are really tables used to provide dropdown values for the "Capacitors Table" connected through a foreign key.

PS: Based on your image "Capacitors Table" has a space in it. Do not have spaces in any names. It is a major headache.

 

[The_Doc_Man]

I'll toss my 2 cents' worth into the hat as well.

When you have many capacitors and many possible distributors, you potentially have the makings of what is called a Junction Table. (Look this up in-forum using our search, or do a web search.) In particular, if some distributors only carry big can capacitors and others don't offer can-type capacitors at all, then you would need a junction table to list the places where you could get each type of capacitor. Note that this would be different if ALL distributors always stocked ALL types of capacitors, because then you wouldn't need a junction table at all.

The same would be true for the relationship between manufacturers and capacitors, and might also be true between manufacturers and distributors (like, if distributor #1 only carries TI and Fuji parts, but distributor #2 only carries Smith, Jones, and Green capacitors... making up names as I go...).

To understand real-world relationships, your analysis needs to look for three cases.

- The 1-to-1 relationship, which is EXCEEDINGLY rare to see in Access because usually when that happens, you just include everything in one table. (But if you have one, Access supports it.)

- The 1-to-many and many-to-1 relationship, which is incredibly common and is supported by Access. For instance, if you had a "Bins" table (showing that all of the ceramic 10 microfarad capacitors were in shelf 6, bin 9) then there would be a 1/many between location and part number. (And if you ever mix capacitors in a bin or have multiple bins for a given size, this becomes the case of the many/many relationship, see below.)

- the many-to-many relationship, which is very common in the real world, such as having more than one source for more than one part, though not all parts are available from all sources. The junction table is needed for this case because Access doesn't directly support this with a unique type of relation. So this one, you roll your own using the junction table.

plog suggests that you have too many tables. (I am reminded of the line from "Amadeus" about "too many notes"...). Steve, however, notes that maybe that isn't so, if you use the tables in a form to drive a drop-down selector (combo box) so that when entering data, you can just choose which of multiple options you were entering.

Therefore, get ready for this: Both are right. You don't need to make some complicated formal relationship between the main table and the tables that define your list of options, though it wouldn't be WRONG to do so. You just might never directly exploit those relationships. Where you would use them is in forms offering choices that are enumerated in the lists. Where I'm coming from is that the form wizards for combo boxes just LOVE it when you can tell them "pick an answer from this table." They do a great job in building that combo box. And if you have more than one field, you can tell the wizard which one to store as the value for that combo box. In this case, IF you chose to define the relationship, it would be 1/many (one field, many possible values).

I'll also second Steve's comment about spaces in ANY structural name, because spaces force you to always bracket that name in []. Further, this is a learning experience for you, but in the future, if you ever needed to up-convert a company design from all-Access to Access front, SQL back - those spaces would haunt you terribly.

 

[jdraw]

Exxili,

You have gotten a lot of good feedback to consider.
I think we really need to see the answers to Markk's questions/comments in post #3.

The "business facts" are essential to getting tables and relationships designed and tested to support that business. I would not jump too quickly to building a physical database. You can do lot of "what iffing" with business facts, some sample data and a pencil and paper to evolve a data model that meets your needs. I say evolve, because database design is often quite iterative. It all starts with a clear description of the business--(who, what, when, where, how much and how often) - starting at the 30,000 ft level and adding detail, review with others, test, and repeat until there is consensus.
Then, you have a blueprint for the database.

See my stump the model approach here.

Good luck with your project.

 

[Exxili]

I'd Just like to thank everyone here for their replies, So far they have helped me quite a lot in understanding of this and development of my template. I was away most of the weekend and must apologise for not replying to everyone here sooner!

You've made a mistake in the right direction. By that I mean, you've over-normalized your data.Considering the alternative (no normalization) that is a good thing--you've read up on it and have an understanding of it.

Thank you for your words! I have indeed been reading quite a lot about relational databases but I feel like I am only just starting to get my head around some of it.

Lastly, avoid synonyms. You have a Distributors table, but all the fields in it are prefixed with 'Supplier_', I assume a Supplier is a Distributor. Looking in from the outside, I'm not sure because you've used 2 different terms. Pick 1 term and live with it everywhere--otherwise confusion creeps in.

You are correct, Supplier is and should actually be Distributor, That is a mistake on my part and thank you for pointing it out! Actually a bad habit of mine when designing these tables.

What is your purpose? What do you want the database to do? Without knowing that, we can't really say how to structure or relate anything. For instance, if this system is for buying parts we would not have the Manufacturer and the Distributor as equals in the data, since we will not buy from the manufacturer. The manufacturer data is simply an attribute that a part may have, AND our internal part number may be able to be supplied by different manufacturers. Maybe we have an internal part number for 3/4" plywood, but we can buy that product from Weyerhaeuser or Canfor. Maybe the distributor sells those as different part numbers, but that's what we need to know first. What are the rules that govern that data? That is what determines your table design and your relationships, and that is determined by your purpose.

Similarly, if we are creating a purchasing system we may not need to know the capacitor type or ceramic size as critical parts of our data system. Maybe these are specs we want to maintain with our parts, but strictly for purchasing and stock management, it doesn't matter.

This system is indeed a template for a Purchasing System, It is something I am doing in work internally however I am trying to learn more about it in my spare time by doing this template.

I haven't got as far as the purchase orders section yet

To answer your questions however

1. What Data Do I Enter? -- Entering Data will be in the form of the Part, Adding new Parts to the capacitor table and then storing them with their values as well as physical location stored.
   
   Next will be in the production of Purchase Orders, a Form to create the purchase order will be designed, with possibly a subform for selection of (single and multiple)Components from Capacitor Table (there will be other tables as well, Resistors etc)
   
   
2. What Data will the System produce -- The Purchase Order Form will then produce a report, and Add the details from the Purchase Order form, and Parts Selection Subform to a premade Purchase order Template.
   
   It will populate Distributor Address, Name, and a table with the parts selected for purchase.

Also Thank you for your information regarding Quantity of Stock, I think i Will actually use that model instead of just Storing it as a value. Makes a lot more sense that way to see the history of what has been added and what has been removed.

I'll also second Steve's comment about spaces in ANY structural name, because spaces force you to always bracket that name in []. Further, this is a learning experience for you, but in the future, if you ever needed to up-convert a company design from all-Access to Access front, SQL back - those spaces would haunt you terribly.

That is indeed my progression path to SQL and a C# or possibly ADO.net Front end. However I figured i would start off small learning the theory behind Databases using Access Databases first. However Thank you for your large post it has helped me quite a bit already. I will also keep in mind Spaces in Field names and Table names.

Could you tell me why they would haunt me terribly, I am assuming SQL does not like Spaces at all? Edit: Further to this, Does access and SQL mind Underscores? e.g Capacitor_Table?

 

[plog]

Non-alphnumeric characters in table/field names require you to use an escape character when you reference them. Specifically, that means you have to use brackets:

[This Field Requires Brackets Because Of Spaces]
[ThisOneHas(Parenthesis)AndRequiresBrackets]
ThisFieldDoesNotRequireBrackets
Nor_Does_This_One

Non-alphanumeric characters just require you to remember 1 more rule down the line. Avoid those weird characters and you avoid having to put brackets around everything to tell the computer where each field name starts/ends.

Underscores are fine as are numbers--but not as the first character. Always use letters as the first character as table/field names, then you can use underscores and numbers however you wish after that and you won't need brackets.

 

[The_Doc_Man]

Could you tell me why they would haunt me terribly, I am assuming SQL does not like Spaces at all?

plog answered this, but I'll be specific in a different way. It isn't that SQL hates names with spaces, because Access back-ends (BE) also uses SQL. My comment was related to a potential future time when some business-related Access BE got so big that you needed to upgrade it from Access BE to some other BE server with larger capacity. The comment is specific for OTHER engines whose SQL will not allow spaces, bracketed or not. Some don't like underscores, either. So my comment was to break the habit before it gets ingrained and that way, to never put yourself in a bad situation when converting a BE file to a larger platform.

 

[gi0rg0s]

A rule I use, is that you shouldn't have a table with one real field (autonumbers don't count as real data).

I am very new to Access and programming, so I may be wrong, but I am using one real field tables for the following reasons:

1. Database is smaller as the size of the Long Integer is generally less than the size of the text field. I don't know if this will ever actually scale to anything significant though.
2. There is no performance cost if you are not joining the tables and the "Type" field is used only to distinguish entries. For instance you will simply query

   WHERE Type = 2

   Instead of

   WHERE Type = "ThisType"

   The only problem is that code is a bit more difficult to read as 2 is far less intuitive than the actual description of Type
3. That field may actually become more significant in future instances and normalizing the database may be time consuming
4. As opposed to the above de-normalizing sounds easily achievable with a Select Case statement. Never actually tried it though.

Any thoughts on this? Am I over-normalizing? Is the following statement a good rule of thumb?

"If you are not facing performance issues, you have not over-normalized your database"

 

[The_Doc_Man]

The rule about "never use a table with only one field" is a personal rule not uniformly shared by all. You use a table with a number and text when you apply that table as a validation table or as a driver for combo-boxes when you have a relatively large (or dynamic) list of possible choices. The text might be used for reports but you would store the code in the main table and only pull up the text when you need translation. As you point out, the code is easier to handle than the text.

Your point #1 is not terribly significant unless there is a large disparity between the size of the text and the size of the code field AND you have a LOT of code values. Like, maybe thousands. Otherwise, the size difference is insignificant.

Your point #2 is correct. The solution for the text lookup issue might be a query that JOINs the main and translation tables.

Regarding your point #3, I am of the opinion that if you think you MIGHT expand the purpose of the narrow table in a future iteration of your database, perhaps you should normalize it now and let matters develop as they come.

As to point #4, using a SELECT CASE works for a code with limited range when writing up some complex logic, but for reports and forms that must translate the code to something meaningful, it is easier to do a JOIN or base the displayed value on a DLookup as the value for the implied text box. Also, if you have to do this translation in more than one place, you will need either a Function to do the translation so that SQL can call it, or you could just do the blood JOIN in a query and not have to write extra VBA at all.

 

[SJS66]

Good topic with some great advice. I keep thinking of that line from "Welcome Back Kotter", that Vinnie Barbarino used to say all the time..."I get so confused"....lol. Some of it I'm still trying to get my head around, but I'm getting there. This relationship business can get a little tricky though;-)