Advice on setting up these tables properly

[bigalpha]

Clin Table Data screenshot:
This is my table of CLIN numbers; they are basically charge numbers. The numbers under Contract 1 (ClinContractID) are the total charge number; there is no suffix. The numbers under Contract 2 are the 'base' and there is a table of suffixes. Every CLIN will not use every suffix.

PBC Grid:
This shows how some CLINs only have some suffixes.

Table Relationship:
Shows my base CLIN table (tblCLIN), my link table (tblClinLink2) and my suffix table (tblClinUnit).

Clin Link Table:
In my table tblClinLink2, I pre-populated each base CLIN with a reference to it's suffix. This way, I can create cascading combo boxes.

Situation:
We currently use only the numbers under Contract 1. We will be switching over to the contract 2 numbers which use a suffix at some point. I currently store the CLIN number in a master table of all products we have sent out for disposal.

When we change to having CLIN + suffix, I'm not exactly sure the best way to transition to storing the data along with setting it up so I can still have cascading combo boxes.

I'm concerned because I'll need to account for the suffixes instead of only just the base number.

 

[MarkK]

There are two problems to solve in table design, 1) did we conform to normalization rules and best practices, and 2) is the design suitable for the problem it is intended to solve.

I can't answer 2) because I have no idea what a clin is, or the significance of its suffix. I can't answer 1) because I don't understand your overall design. Given the images, it seems that there are other relevant table relationships you haven't shown.

One observation I do have is that your tblClin contains a cost field and a unit field, but it looks like that data ought to be handled by the related tables.

hth

 

[bigalpha]

Our facility takes in containers of waste and then we take those and ship them out for disposal; so it's essentially an inventory database.

I have a master table of each item we get (and subsequently send out for disposal). In this master table is ClinIDFK; where I record which CLIN number from tblClin. It currently only stores one value. This is the only other table in my entire database that CLIN is used for.

A CLIN number is a lot like a UPC code - it's used for one product, regardless of size. That's how the current CLIN system is set up.

However, it's changing to a new system that uses a suffix to designate size so there are two parts to each CLIN instead of one.

For example:
CLIN: BA01 is for whenever we have a container of alkaline batteries.
Suffix: C1 is when that container is 0-5 gallons in size.
The end result is BA01 C1.

I created the link table so I can narrow down which suffixes (C1) belong to which CLIN (BA01), since not all CLINs will be shipped in all sizes. I did this so I can maintain a set of cascading combo boxes that will only show relevant suffixes (C1) for each CLIN (BA01). Also, the cost is dependent on CLIN (BA01) and suffic (C1). That's what the screenshot of the excel sheet shows.



The fields ClinCost and ClinUnit that are located in tblClin are there because the current set of CLINs require NO suffixes and I only had this one table. I am mid-process in figuring out how to merge the two different systems and got stuck figuring out the best way.


I made one table of CLINs, one table of suffixes and then a link table to join them because I thought that was the correct method.



So I don't what the best way is to relate each CLIN (BA01) with it's suffix (C1) and then record that against each container we receive.


I hope that makes sense.

 

[jdraw]

I agree with lagbolt - it's hard to understand what these tables and terms represent. I suggest you try to stick with 1 fact 1 field and not
concatenate "concepts" CLIN & BA01 & C1. You could be digging a hole.

Can you describe your business in terms like Containers, Contents...?

The BA01 type codes are Environmental codes if I recall correctly.

 

[MarkK]

A common pattern in data is that there is an abstract reality, which must be stored, and then there is the concrete reality that conforms to the abstract pattern, and it must also be stored.

An example of this is a school course catalog, which is the abstract reality. Then there is the matter of unique instances of each course, which may or may not be offered this semester, which have a room number and occur at a particular time, and of which there will be many instances over the years.

At first glance your problem feels like this, that there is an abstract labeling scheme, and underlying that will be multiple instances of real-world stuff that will need to get labelled. But the label and the thing are maybe more separate than you think?

Where will you do most of your data-entry? Like, what information will you produce a lot of? That is your real-world stuff, and the abstraction, like fixed clin numbers and suffixes, will act as tags on your volatile data.

Does that help any?

 

[bigalpha]

Hmm.

We get containers of products. We take those containers and we ship them. Each container costs us a certain amount of money - according to a particular charge number. Currently, it's just one number. This one number can be used on multiple types of containers of any size. I store this number in the table that records the rest of the information about the container.

When I initially set up the tables, it was just one table with these single charge numbers and how much it costs for each.

The new system that's going into effect will require two charge numbers for us to price each container. A base number for a specific product inside a container, then a secondary number that corresponds to the size of the container.

I have a table of base numbers, a table of secondary numbers and a linked table that shows the possible combinations of the two. Each of these combinations will have it's own cost.

Also, I wasn't going to concatenate the numbers and store them - I just meant that both numbers will be required for each container.

 

[MarkK]

OK, so in the images you posted there is one that shows a many-to-many relationship. Is that the one that defines your cost? And if so, I bet you want to link your volatile data as a child of that join table.

Is that what you had in mind?

 

[bigalpha]

Yes, one base number can have many secondary numbers. The secondary numbers can be used for many base numbers.

Unfortunately, they haven't finalized all of the costs so I don't have the numbers for each unique combination of base and secondary numbers.

Is my current table structure okay?

 

[MarkK]

I think you said yes to a different question than I asked.

I don't know what your table structure is. How are your other tables related to your cost tables?

 

[bigalpha]

Oh. I don't have different cost tables. I built the cost into the three tables I have.

 

[jdraw]

I recommend you create a logical data model that supports your business, and not just focus on the current physical database.
eg

I built the cost into the three tables I have.

If your business is changing, then something in the database will likely change. Here is a tutorial with steps that will lead you from business definition to normalized tables with relationships. Get the logical model set up and tested with test data, then compare your current database structure with the logical model; and adjust as appropriate.

Good luck with your project.

 

[bigalpha]

I recommend you create a logical data model that supports your business, and not just focus on the current physical database.
eg


If your business is changing, then something in the database will likely change. Here is a tutorial with steps that will lead you from business definition to normalized tables with relationships. Get the logical model set up and tested with test data, then compare your current database structure with the logical model; and adjust as appropriate.

Good luck with your project.

The only thing changing is how we assign a cost to each container. Instead of one charge number, it now will require two.

When I separated the two cost numbers and created a linked table between them, isn't that normalizing the data properly?

 

[MarkK]

I think that what you've shown looks OK, but I have no idea how what you've shown is related to the rest of your system.

But here's where the rubber hits the road: is it working for you? Can you add the actual data that your real-world problem produces? Can you return the results that you need? If rates, prices, or settings change, can you do those updates without breaking the integrity of your historical data?

If you answer yes to those questions, you don't have a problem. If you have a specific failure going forward, then lets look at then when, and if, it happens.

 

[bigalpha]

It's working for me now - when all I have to do is enter a base number. I can easily link that number and it's cost to each container.

But when the new system kicks in and each container will need a base number and secondary number; and the cost is dependent on both of those numbers together is when I'm going to have issues with my current set up.

I uploaded the 4 tables that are applicable to our discussion in case that will help clear things up some.

 

[MarkK]

This is what I would expect, that your cost is a combination of the two other factors, CLINType and CLINUnit, and that that cost is then Parent of some other thing, like a product, that you will buy and sell. Then that product is linked to an order . . .
See if that makes sense. See how the product item need only reference the cost (link) table, and that table links to the two cost factor tables, type and unit.

 

[bigalpha]

While the cost information may be the parent to the CLINtype and CLINunit, we look things up by CLINtype and CLINunit to get the price since there's so many CLINtypes.

I see how your example would work but the products we deal with aren't clear cut where I can have a product table, per se.

For example, we will have a CLIN for bottles of waste acid that are under 5 gallons in size. This encompasses all acids; and we don't have a way to know which ones we will get. So we would look up the CLIN for waste acids under 5 gallons in size and then associate that with our main table.

 

[MarkK]

Hey, I don't know.

So CLINType is "bottle of acid", and CLINUnit is "<= 5", and follow those two IDs to the CLINCost table, and doesn't that give you the cost? Then your system consumes that cost somehow, so not a product table, a "main table." Is that right?

How does that fail? What doesn't work about it? What specific operation do want to perform that fails?

 

[bigalpha]

Hey, I don't know.

So CLINType is "bottle of acid", and CLINUnit is "<= 5", and follow those two IDs to the CLINCost table, and doesn't that give you the cost? Then your system consumes that cost somehow, so not a product table, a "main table." Is that right?

How does that fail? What doesn't work about it? What specific operation do want to perform that fails?

Yeah, that's how our process works, and how we get the cost. We pick CLINtype, and the CLINunit is narrowed down. And the cost is associated with the unique combination of CLINtype and CLINunit.

I haven't set up the operation too 100% completion because I wanted to make sure my table set-up and logic was correct.

 

[MarkK]

OK, so as far you're concerned this thread is solved?

 

[bigalpha]

I suppose so. I didn't see any qualms about the way I set up my tables.