Looking for db design suggestion

[Jamescdawson]

Hi All,

I have a db which records incoming specialised waste. The waste arrives from Clients in a variety of containers (11 different sizes of container) There are normally 2 or 3 sizes of containers per consignment but it can on occasions be more. (Herein lies my problem.)

I could create a data entry form with 11 lines (one for each size of container).... No very elegant !

I need to record the Quantity of each size of container and then calculate total weight received in THAT size of container. Each size of container has an allocated weight factor

2 x 4 Litre (2 Kilo)
3 x 7 Litre (3 Kilo)
1 x 11 Litre (5 Kilo)



I have the 11 container sizes in their own table with the necessary associated data.

At the moment, my data entry form has four lines - 1 for each each container size in the consignment. Each line contains QTY Field and a Container Size Field. There is a third field which displays the total weight and I use a DLookup() to extract the correct weight for that size of container from the Containers table and mulitply by the QTY.

The problem is self evident... If I have five different sizes of container I'm 'screwed'. I also have difficulties counting the QTY of each size of container given that there could be any one of 11 different containers on any of the lines.

What IS the best way to construct this database ? Should I resign myself to a separate line for each container size or is there a more elegant way for me to do this, bearing in mind that I must be able to count the number of each size of container received that week.

Any suggestions would be gratefully received.

=Jimbo=

[orange]

Do you have a data model (table and relationships)? It seems to me that you have to get the appropriate database designed to meet your business needs before you design/redesign a form(s).

From your post, your main Entities are

Clients
consignment
containers

There are currently 11 container sizes
Each size of container has an allocated weight factor


You have created a Container table with the necessary associated data.

So buisness-wise

You have many Clients
Clients bring their waste to you in Consignments
A Consignment may involve 1 or more Containers
The Container(s) in a Consignment may be of different sizes
Currently there are 11 distinct ContainerSizes
A Client may have 0 or more Consignments per Day


I have attached a sample data model. This can be adjusted as needed pending additional facts/assumptions.
The assumptions in the model:
All containers in a consignment are processed at one time (Day)
Containers are not individually identified, but are identified by ContainerType (size)


This is saying A Client can have Many Consignments
A Consignment is for a specific Client and may involve 1 or more ConsigedContainers
Each ConsignedContainer is a specific ContainerType
Each ContainerType has its own specific Name, Size and Weight

[Jamescdawson]

Hi Orange,

Thanks for your post. You have it in a nutshell. I will consider your suggestions in more detail in the coming weeks as I try to implement it. In truth, the database was started as a simple entity and has grown way beyond the original plan into a complex beast. [To get to where I am now, you wouldn't want to start where I started... !!] The part you've given the advice on is a small part of the whole. Had we known it was going to become this complex, we may not have built it like we did.... In fact, we may not have built it at all.

I've sought the input because I want to improve the whole and I'm trying to introduce better db best practice with the newer elements.

Thanks very much for the input.

=Jimbo=