Primary Keys and Indexes

[Cowboy]

So a quick, upfront "Thank you" for anyone who can offer advice on this!

I have a db to handle theatre lighting gel stock that I am working on. I have two tables, one for manufacturers and one for the gel inventory.

tblManufacturers

ManufID (autoIncrement PK)
ManufName (text)

​

tblGelStock

ManufID (FK part of composite PK)
ColorNumber (part of composite PK)
SL (int)
8Par (int)
10Leko (int)
12Fres (int)
Spot (int)
Cyc (int)
FullSheet (int)
Description (text)
​

So I guess my question is, should the tblGelStock have an additional field, auto increment "GelID" primary key, and just index the ManufID + ColorNumber?

The reason I went with the composite primary key in the first place is because more than one manufacturer may have a gel with the same color number, but no manufacturer uses the same color number more than once.

There are currently 442 records, and I do not foresee the db holding more than probably 2.5 times the current records.

Also, is this considered normalized? I am a bit confused on the normalizing bit...

Thanks for your help!

 

[The_Doc_Man]

Cannot tell about the normalized part, but that set of fields that begin with 8, 10, 12 look suspicious. By any chance are those being treated as check-boxes to show that the gel in question is a particular type of gel? If that is correct, then "Not Normalized" is my answer.

As to (use compound key) vs (use autonumber key), the question is why you need a key. No, not being flippant here.

Unless there is a relationship such that tblGelStock will be the parent of a one-many relationship, you don't need a PK. By contrast, your Manufacturer table needs a PK because it is the child of a many/one relationship.

For your gel stock table, assuming the simplest possible case, you can use queries to provide sort-order appropriate to what you are doing without ever dealing with a PK. This is not to say you shouldn't make a PK. Some purists say you NEVER make a table that doesn't have a PK. I'm a pragmatist. I at least ask the question before I make a PK.

 

[jzwp22]

I would recommend an autonumber primary key field in every table, and I generallly do no use composite keys (actually I never use them).

If a gel stock item can be provided by multiple manufacturers, then you have a one-to-many relationship which means the manufacturer cannot be in your tblGelStock because that would limit you to only 1 manufacturer.

I do not follow your discussion regarding color number. It sounds like the color number is not a standard color number, but it sounds more like you equate the color number of one manufacturer to be equivalent to a color number of a second manufacturer. In other words, even if the color numbers are different between manufacturers, you still consider it the same color for a particular gel stock item. Am I interpreting that correctly?

Can you explain what the other fields in your table represent?

SL (int)
8Par (int)
10Leko (int)
12Fres (int)
Spot (int)
Cyc (int)
FullSheet (int)
Description (text)

 

[Cowboy]

Thanks for the responses.

Some clarification:

The SL/8Par/10Leko/12Fres/Cyc/Spot/FullSheets are all the sizes of the gel, but are fields that hold current inventory counts.

As far as manufacturers, for this database, a manufacturer can provide many stock items, but no two manufacturers can provide the same stock item (in this case, Rosco gel color number 88 can only be provided by Rosco, Lee gel color number 88 can only be provided by Lee).

Color number is how the manufacturer states the "product number" for a specific gel.

ex:

Lee(manufacturer) has a color with color number 88, its description is "Lime Green".

Rosco has a color with the color number 88, its description is "Light Green".
​

Now, in the example we might have 10 SL sized cuts of Lee's 88 and 20 SL sized cuts of Rosco's 88.

In this case, the two different brands have similar named and color gels but not always the case.


So I can't use the color number (ex: 88) as a unique index because there are two values of 88.

Now this is currently only inventory for one location, there may be a new table added tblVenue for the two buildings we have that contain two separate inventories of this gel, so I might add a new FK venueID into the tblGelStock, but am currently only focusing on one inventory data set.

As far as manufacturers, for this database, a manufacturer can provide many stock items, but no two manufacturers can provide the same stock item (in this case, Rosco gel color number 88 can only be provided by Rosco, Lee gel color number 88 can only be provided by Lee).

So, yeah...don't know if that actually clarified things, but thank you for the help!

 

[jzwp22]

Irrespective of the details, you have manufacturers that you buy gel stock from; the gel stock comes in multiple colors and each color can come in multiple sizes

tblManufacturers
-pkManufID primary key, autonumber
-txtManufacturerName

tblSizes (1 record (not field) for each of your sizes: SL/8Par/10Leko/12Fres/Cyc/Spot/FullSheets)
-pkSizeID
-txtSize

A manufacturer can supply many colors:

tblManufacturerColors
-pkManColorsID primary key, autonumber
-fkManfID foreign key to tblManufacturers
-ManufColorNumber
-ManufColorName

Each manufacturer-color combination can come in various sizes

tblManufColorSizes
-pkManufColorSizeID primary key, autonumber
-fkManColorsID foreign key to tblManufacturerColors
-fkSizeID foreign key to tblSizes

Now you also mentioned that you want to keep track of inventory at multiple locations, so it would be best to set this up now otherwise you will have to redesign everything later on.

tblLocations
-pkLocationID primary key, autonumber
-txtLocationName


tblLocationInventory
-pkLocInvID primary key, autonumber
-fkLocationID foreign key to tblLocations
-fkManufColorSizeID foreign key to tblManufColorSizes

Now for each location/item you will have many inventory transactions (buy, sell, scrap, transfer) (to get current on hand inventory, you would add/subtract the various transactions)

tblLocationInventoryTransactions
-pkLocInvTransID primary key, autonumber
-fkLocInvID foreign key to tblLocationInventory
-dteTrans (transaction date)
-longTransQty
-fkTransTypeID foreign key to tblTransTypes

tblTransactionTypes (buy, sell, scrap, transfer)
-pkTransTypeID primary key, autonumber
-txtTransType


Now lets say that manufacturer #1 has a gel that is called light red while manufacturer #2 has a gel called pastel red. You consider these two colors as substitutable for one another in your application. You can create a table that associates the two manufacturers' colors so that if you are out of stock on one, you can substitute the other. Considering that you may have multiple manufacturers that have equivalent colors for your purposes, that would be a many-to-many relationship.

tblAllowableSubstitutions
-pkAllowSubsID primary key, autonumber
-fkPManColorsID foreign key to tblManufacturerColors
-fkCManColorsID foreign key to tblManufacturerColors

BTW, trying to do this last table using composite foreign keys would be a nightmare.

 

[Cowboy]

Okay so I am getting a little confused as to why everything gets broken up into different tables.

I am also confused as to the the inventory on hand issue.

It seems like rather than just storing in a table with a row for each color and fields for each count you're suggesting track the changes made to each color's individual size counts in a separate table.

So if we buy 5 full sheets, cut 2 of them down to 12 SL cuts each, there would be a transaction in the table of:

Rosco 88 +3 full sheets + 24 SL cuts.

And I'm guessing each time we need to get the current count you are suggesting put a calculation in? So if over the past 3 months we've had 50 "transactions" where we've bought new gel or thrown out old gel it would need to make a sum of 50 different +/- transactions?

Is that better than just updating the record?

Example:

Original Count: Rosco 88 size SL at Location A = 10
Update Count: Rosco 88 size SL at Location A = 34

​

I completely understand doing the location bit NOW rather than later.

The substitution aspect I think I'm going to leave out for now as I am already entering into some scope creep.

 

[jzwp22]

Okay so I am getting a little confused as to why everything gets broken up into different tables.

I am following the rules of normalization. For a brief summary, check out this site. For a relational database to work properly, the tables have to be normalized. The table structure is the most important part of a relational database. There is quite a learning curve when one starts designing a relational database.

I am also confused as to the the inventory on hand issue.
And I'm guessing each time we need to get the current count you are suggesting put a calculation in? So if over the past 3 months we've had 50 "transactions" where we've bought new gel or thrown out old gel it would need to make a sum of 50 different +/- transactions?

You would create a query once and just run the query to get the quantity on hand at any time. You can filter the query to get specific items or all items. Here is another site that discusses quantity-on-hand using a more rigorous approach. Inventory control is one of the more challenging database applications to model.

The substitution aspect I think I'm going to leave out for now as I am already entering into some scope creep.

Not a problem. It was just something that came to mind that I thought might be helpful.

 

[Cowboy]

Thank you for your help with this! Time to get down to starting fresh on the design and I will see where I get with the nuts and bolts of this thing lined up for now.

Thanks again!

 

[jzwp22]

Feel free to post back with any questions.

 

[Galaxiom]

I would recommend an autonumber primary key field in every table, and I generallly do no use composite keys (actually I never use them).

I disagree. Adding a synthetic autonumer key to a table that already contains a suitable natural key field is simply adding processing for no benefit.

In this case the table already needs a index on the ManufacturerID/Color combination to avoid duplicates. It might as well also be the Primary Key if one is required for that table. Especially since these fields are (short) integers.

Adding an unnecessary key is even more pointless.

I will echo DocMan's comment. Not all tables need a Primary Key. Generally, if they are not related to a parent table they do not need a key.

 

[gemma-the-husky]

cowboy

its important to understand what a database is, and is not - although a table it looks like a spreadsheet, its radically different - getting into spreadsheet thinking goes the wrong way completely.

everything gets broken up into tables, because thats what a database IS - basically you dont want to repeat information on different rows, about the same thing. And the process of analysing the data structure is called normalisation. And note that the usage/application of the database should be separate from this analysis. Its sort of function follows form. Design the form/layout cvorrectly, and the functionality becomes much easier.

so the docman's query about the number prefixes seems spot on the money.

-----------
look at it another way. each item of gel is of a specific type. therefore what you need first is a table that identifes all the different products you sell. Now is a product different because it comes from a different manufacturer. It may or may not be - so you need to understand your data. So as part of this, you allocate each product gets an ID no. (eg the autonumber) Now you COULD use the product name for this - but this introduces problems later on

now you need a transactions table that records movements to these products. (distinguishing between stock coming in and going out) - and the only identification of the product you need in this table is the ID no as above. That way, you can change the description (say), without any change being necessary in related tables. Note that if you had used the product name as an identifier, then you would need to change the product name in all the related tables - which you want to avoid, because its inefficeint. A number also takes up less space in a database.


now the quantity you have of any given product is given by summing the transaction counts - and access makes it really easy for you to do this over the whole population - so you could maybe pick RED gels products from the product list, and then just sum the movements for that selection of products

Hope that makes sense.


notice that the product itself is immaterial for this exercise - HOWEVER, if you have certain types of products (as indicated by the 8, 10, 12 prefixes etc) then you should be able to use a single field (maybe a combination of fields) on the product type to give you this analysis.

Its hard to be precise, becuase its an art as much as a science, but once you've done the process the first time, it should become clearer.

 

[The_Doc_Man]

Let me murk up the waters in my own little way. Or maybe clarify them.

Cowboy, you asked for design advice and got an earful on splitting up tables due to issues in normalization. I'm going to guess that your first impression was "Whoa! What did I just jump into." And those Access users who started from spreadsheet environments have ALL had that "Whoa!" moment.

We suggest scrupulous normalization because Access works BEST with normalized data. Not to say that it WON'T work with unnormalized data, but a lot of Access functionality is based on a normalized model. When you have a normalized model, Access helps you behind the scenes in numerous ways in terms of auto-generating forms and reports. You can build queries that automagically know about relationships defined between two or more tables. The builder wizards take full advantage of that information.

That is not to say that you couldn't just treat everything like a flat-file in a single table, because Access can do that, too. But it won't help you behind the scenes quite so well. Further, if you were going to treat it as a flat file, why bother migrating from Excel?

When you asked the question you did, you embarked on a journey from a flat-file mind-set to a multi-dimensional mind-set. It really doesn't happen overnight, so if you feel overwhelmed, ... that's about par for the course at the stage you appear to have reached. As you progress, you WILL begin to see Access helping you. But like anything worth doing, normalization is worth doing well - and so is the design process.

So... what's the payoff? Well, that's hard to say. But Nicklaus Wirth, the creator of the Pascal programming language, was once quoted as saying that 80% of all programming problems were related to poor data design. We can quibble over 80%, 90%, 70%... but it is WAY more than half of your problems will stem from data design flaws. So your payoff is that once you get your data designed correctly, you should have less work or easier work in the detailed implementation stage of forms and reports.

Of course, you are currently smack in the middle of that 80% of your problem. So it seems bewildering. It will get better. And each subsequent Access project will be easier once you have changed to the multi-dimensional mind-set you need for working with Access data tables.

Good luck on delving into the vagaries of data design. And don't hesitate to come back here when you have specific questions or guideline questions.

 

[Cowboy]

Thanks to everyone for the tips and advice.

Yeah, I'm pretty much sub-par for where I should be DB design-wise. I should have payed WAY more attention to the DB design and E-R diagram class I had in college.

I basically have modeled the DB on the tables suggested here as far as splitting all the tables up. The only factor I am not including in this is the "substitute color" - for the design scope creep that would ensue with functionality like that.

So as I have been data-infusing my tables from scratch now, I came across the question of: How do my starting values for my stock get handled? Where do I put those values in?

Would it be easiest to create a form that will allow me to select the color and create an "ADD" transaction and go one by one through my list?

Thanks again for taking the time to help me learn this!

 

[jzwp22]

This site from Allen Browne discusses how to handle inventory and quantity-on-hand

 

[Cowboy]

I've basically finished populating the table for tblManufacturerColors. Now, how should I populate the tblManufColorSizes table?

If I am looking at this correctly, I would have to have the ID from tblManufacturerColors used for each size ID from tblSizes, correct?

tblManufColorSizes has a primary key pkManufColorSizeID (pk), and two foreign keys, fkManColorsID (fk1) & fkSizeID (fk2).

So if I have say, 7 different sizes, is this basically what my tblManufColorSizes should look like?

pk __ fk1 __ fk2

1 ____ 1 ____ 1
2 ____ 1 ____ 2
3 ____ 1 ____ 3
4 ____ 1 ____ 4
5 ____ 1 ____ 5
6 ____ 1 ____ 6
7 ____ 1 ____ 7
8 ____ 2 ____ 1
9 ____ 2 ____ 2
... ___ ... ___ ...

(sorry, best way I could figure to draw a table in-post)
​

If this is the case - is there an easy way to populate this table?

Thanks!

 

[jzwp22]

What you show is correct in how the table would look when populated.

In terms of actually populating the table, if each manufacturer offers each color in all sizes then there is a way populate tblManufColorSizes with an append query. I would start with a regular SELECT query that uses both the tblManufColor and the size table with no join between them. This creates a Cartesian product of the two tables. Select the pkManColorsID and pkSizeID fields from their respective tables. Change the query to an append query with the destination table being tblManufColorSizes. You'll have to tie the pkManColorsID field to the fkManColorsID and similarly for the sizeID fields. When you run the query it should append all combinations.

 

[Cowboy]

Okay...I think you've lost me!

So let me see if I can try to understand this.

I create one SELECT query, within which the FROM would be tblManufacturerColors and tblSizes?

SELECT tblManufacturerColors.pkManColorsID, tblSizes.pkSizeID
FROM tblManufacturerColors, tblSizes;

Then go into the query and change it to an APPEND query with the destination of tblManufColorSizes.

This is where I get really confused - the tying it together part. Is this where I connect the fields in the related tables in the relationship window?

Now, when all is said and done, if I add a new manufacturer color - will I get duplicate results if I run the same query again?

Thanks for your time on this!

 

[jzwp22]

Your select query is correct

Then go into the query and change it to an APPEND query with the destination of tblManufColorSizes.

This is where I get really confused - the tying it together part. Is this where I connect the fields in the related tables in the relationship window?

No, in the grid part of the query window, you have to associate the two fields of the query to the corresponding fields of tblManufColorSizes. If you want to do it in SQL view it would look something like this:

INSERT INTO tblManufColorSizes (fkManColorsID, fkSizeID)
SELECT tblManufacturerColors.pkManColorsID, tblSizes.pkSizeID
FROM tblManufacturerColors, tblSizes

if I add a new manufacturer color - will I get duplicate results if I run the same query again?

You will have to set criteria (WHERE clause) in the query to only include the new manufacturer otherwise you will duplicate everything in the table.

 

[gemma-the-husky]

one way of doing all this is to just consider the ENTITIES (ie tables you need) - and then allocate each bit of DATA you need to the correct ENTITY.

its still a "black art" though, until you get used to it, and until you are used to doing it, it can be hard to identify the entities correctly.

What tables have you got at the moment.