Unsure about proper relationship structure

[Chillendw]

Good afternoon everyone,

I was hoping that I could get some feedback on my relationship structure. I am unsure which one would make the most sense regarding the products.

Thanks in advance.

 

[plog]

You've not provided enough information. Can you explain why you are considering each one? Obviously we are talking about wether or not you need a products table.

Please explain how both your actual business works (don't use any database jagon, explain what you actually do as if we were elementary students and you were a career day presenter) and then explain your thinking about having a table for products (feel free to jargon out).

 

[Chillendw]

Please explain how both your actual business works (don't use any database jagon, explain what you actually do as if we were elementary students and you were a career day presenter) and then explain your thinking about having a table for products (feel free to jargon out).

Sorry about that.

Each factor (product name) can have different lot numbers.
Each distinct lot number has a specific weight associated with it.

We can receive the factor, which may be a new factor and/or a new lot number.
We also dispense the factor, to a specific individual.

I initially thought I'd need tblProducts so that each Factor with a different lot number has a specific ProductID associated to it which I can reference.

My confusion started when I was making a form for dispensing. What I wanted was to choose a FactorName --> LotNumbers associated with that FactorName. But since tblOrders contained ProductID as the link to the product, I was unsure how to add the FactorName and the LotNumber if both control sources were ProductID.

I then thought that removing tblProducts would allow me to easily add FactorName and LotNumber.
However, I'm not able to create a query of the products as easily without tblProducts. Maybe I'm just not seeing a way just yet.

Thanks in advance.

 

[plog]

First, avoid synonyms at all costs. Don't use factor and product for the same concept--you will get me lost and cause confusion among your own employees as well. 1 concept, 1 term.

Your explanation is a little too genericized but I can work with it. Let's use Sugar as your factor that you receive in lots and then disperse to individuals.

This is all about how granularly you need to track your sugar. It comes in a lot, but does it need to be dispersed to individuals as such? Do you need to know Sally got 4 lbs from lot 15 and 1 lb from lot 23? Or do you just car that Sally got 5 lbs of sugar?

I mean, once you accept a lot of sugar into inventory do you care if all your sugar gets dumped together in one big vat and then dispersed as needed, or do you need to keep each lot of sugar seperate all the way up to dispersing it?

 

[Chillendw]

This is all about how granularly you need to track your sugar. It comes in a lot, but does it need to be dispersed to individuals as such? Do you need to know Sally got 4 lbs from lot 15 and 1 lb from lot 23? Or do you just car that Sally got 5 lbs of sugar?

I mean, once you accept a lot of sugar into inventory do you care if all your sugar gets dumped together in one big vat and then dispersed as needed, or do you need to keep each lot of sugar seperate all the way up to dispersing it?

When we receive the "sugar" it comes in a box full of "sugar packets." The lotnumber of the box tells us how many grains of sugar each sugar packet has. We can receive Stevia, Splenda, Equals, etc, but they will all have several different lotnumbers to each sugar brand.
When we give it to Sally, we go by grains of sugar (not packets). So each Lotnumber has to be accounted for.

Thanks.

 

[plog]

I think I need the specifics of what your business does and what you hope to achieve with this database. Give me 2 things:

1. A description of what your company does without using any database jargon. Don't tell me about tables or queries or anything about your database. Just tell me what your company does.

2. Tell me what role this database will fill within that business. Here you are free to use jargon, but I advise against it.

 

[Chillendw]

1. A description of what your company does without using any database jargon. Don't tell me about tables or queries or anything about your database. Just tell me what your company does.

I'm interning at a place where they receive certain medications and then send it out to be administered to people.

2. Tell me what role this database will fill within that business. Here you are free to use jargon, but I advise against it.

The database is meant to keep a running balance of each medication. It will also keep records of what was received/dispensed, to whom it was given, and when it was received/given. I also wanted to make a few reports displaying the aforementioned points.

The employees table is there so that everyone can see who input each transaction.

Hope that helps.

Thanks

 

[plog]

First, you don't need tables with only 1 real field of data (autonumbers don't count as real data). So that means as they stand now, you can eliminate tblFactors, tblMfr and tblTransactionType

Also, to sort out your inventory transactions, you should do this in a table that stands between tblOrders and tblLots, I just think the fields you have in tblProducts is incorrect, is poorly named and doesn't allow credits to inventory. Attached is a screenshot of what I believe should be the crux of your tables/relationships.

I believe you shoudl have tblTransactions. This will allow you to both add and subtract from inventory. Let me know if you have any questions.

 

[Chillendw]

First, you don't need tables with only 1 real field of data (autonumbers don't count as real data). So that means as they stand now, you can eliminate tblFactors, tblMfr and tblTransactionType

I think I may do this. It would alleviate some issues I'm having with forms and (cascading) comboboxes.

Also, to sort out your inventory transactions, you should do this in a table that stands between tblOrders and tblLots, I just think the fields you have in tblProducts is incorrect, is poorly named and doesn't allow credits to inventory. Attached is a screenshot of what I believe should be the crux of your tables/relationships.

Interesting. I guess I'd have to use a subform in most of my forms with this layout. And I'm curious about "MedRec."

I believe you shoudl have tblTransactions. This will allow you to both add and subtract from inventory. Let me know if you have any questions.

Thanks for your reply. I was wondering about tblTransactions. I have queries set up with transaction type into a Union query (1 is dispensed, 2 is received) then a Sum query to keep track of the inventory for each Lot. I'm not even sure if it works yet though, but I'm curious about your method.

I've attached my current relationship layout, and what I adjusted it from.

Really appreciate your help though. Thanks.

 

[plog]

I've attached my current relationship layout, and what I adjusted it from.

And which is which? And why even include the old bad one?

 

[Chillendw]

And which is which? And why even include the old bad one?

The one with the adjusted FK names and fewer tables is the newer one. I included both to ensure I was headed in the right direction.

Thanks.

 

[plog]

So 5809 is the one.

That looks good. A few nitpicks:

[Date] is a reserved word. Change that field name to reflect what date it represents-- [OrderDate].


TblTransactionTypes should have a field to denote if that type of transaction is a credit or a debit. In my structure image I named that TransTypeAction. Then for the values I would make it either -1 (removed from inventory) and 1 (added to inventory). Then I would multiply that value by [TransactionAmount] to determine how it actually affected inventory.

Also, I used MedRec instead of PatientID because MedRec should be unique to a specific Patient. When I have a unique value like that external to my system I usually use it, instead of using an autonumber primary key (kind of like if I had SSN I would use it, or if I had a Vehicle Serial number I would use it).

 

[Chillendw]

So 5809 is the one.

That looks good. A few nitpicks:

[Date] is a reserved word. Change that field name to reflect what date it represents-- [OrderDate].

Good point. Changed.

TblTransactionTypes should have a field to denote if that type of transaction is a credit or a debit. In my structure image I named that TransTypeAction. Then for the values I would make it either -1 (removed from inventory) and 1 (added to inventory). Then I would multiply that value by [TransactionAmount] to determine how it actually affected inventory.

I've attached a screenshot of my tblTransactions. I'm doing something similar with the 1 and -1. However, I'm calculating it in separate queries, combining them, then adding them together. I've attached a screenshot.
But maybe your way is more efficient. Or maybe my way won't actually work. tbh, I'm still unsure if my running balance will work, I've been busy setting up the structure.

Also, I used MedRec instead of PatientID because MedRec should be unique to a specific Patient. When I have a unique value like that external to my system I usually use it, instead of using an autonumber primary key (kind of like if I had SSN I would use it, or if I had a Vehicle Serial number I would use it).

I'm understanding what you're saying. That's the PtMedicalRecordNumber (could probably use a shorter name [PtMRN]) in tblPatients. I suppose I could use that.

Thanks for the tips. Appreciate it.

 

[plog]

UNION queries are generally hacks around a poor layout. Your layout doesn't require a UNION to get the balance.

If you had my TransTypeAction field in tblTransTypes, this SQL would give you inventory:

SELECT FactorsID, SUM(OrderAmount * TransTypeAction) AS Inventory
FROM tblOrders
INNER JOIN tblTransactionTypes ON ID_TransactionType = TransactionTypeID
GROUP BY FactorsID

That tells you the inventory of any Lot.

1 more nitpick: tblFactors really isn't a Factor table, its a lot table--that's the most granular object in there, so that would be a better name and that would make FactorID = LotID.

 

[Chillendw]

If you had my TransTypeAction field in tblTransTypes, this SQL would give you inventory:

SELECT FactorsID, SUM(OrderAmount * TransTypeAction) AS Inventory
FROM tblOrders
INNER JOIN tblTransactionTypes ON ID_TransactionType = TransactionTypeID
GROUP BY FactorsID

That tells you the inventory of any Lot.

Added the TransactionTypesAction field and used your code:

SELECT FactorsID, SUM(OrderAmount * TransTypeAction) AS Inventory
FROM tblOrders
INNER JOIN tblTransactionTypes ON ID_TransactionType = TransactionTypesID
GROUP BY FactorsID;

... and kept getting "JOIN expression not supported," highlighting "ID_TransactionType = TransactionTypesID"

So then I thought maybe adding the tables would work:

SELECT tORD.FactorsID, SUM(tORD.OrderAmount * tTRA.TransactionTypesAction) AS Inventory
FROM tblOrders AS tORD
INNER JOIN tblTransactionTypes AS tTRA ON tTRA.TransactionTypesID=tORD.ID_TransactionTypes
GROUP BY FactorsID;

I believe that worked (no info in the table yet). Was wondering if it needed ORDER BY OrderDate Asc.

Appreciate the help.

 

[plog]

no ORDER BY needed to get it to work. Most likely the error message is because those 2 fields are not compatible types (string-number, date-string, etc.) Are TransactionTypesID and ID_TransactionTypes the same data types in the table? They both should be numbers.