Table Structure and Relantionships (1 Viewer)

[tmyers]

As some of you are aware, I have been attempting to make a database to track spaces in a warehouse and what material is in the given space and if a given space is taken or available. After taking several days thinking it over, I scrapped the previous iteration I had and rebuilt it

Attached is what I have put together so far. Are there problems with how I have it built yet? Would you do it differently?
I don't think I am using warehouse correctly as logically, warehouse is the next tier up in the hierarchy. However, I don't need it as we only have one warehouse but tried putting it in for future proofing or the possibly of sub-warehouses.

I am still unsure how I should handle the locations themselves. Should I keep the separate parts of the locations in different tables as I do now? Should I have a master list of all locations in one table (how would I populate that given the amount of possible combinations)? How would you handle instances where an item takes up multiple spaces?

My thought process for the structure is as follows (it might be a little disjointed and not in the right hierarchy):
You have a warehouse
A warehouse consist of many locations.
A location can have a single item, many of a single item, or many different items of various amounts
Items are part of a project
A project can have many items in many locations
A project belongs to a single customer
A customer can have many projects

 

[CJ_London]

Not sure of the benefit of the aisle, bay and shelf tables - presume these are going to be identified as 1,2 3 or A, B, C or similar.

However you might want to consider:

A warehouse has many aisles
an aisle has many bays
a bay has many shelves (min 1)
a shelf can hold many bins (min 1)
a bin can contain only one material (or many materials?)

You can then 'map out' your warehouse

Not clear to me from your description whether the intention is that a project utilises a specific range of locations (i.e Aisle 1, bays 3-5, 6 and 9, plus aisle 3, bay 15, shelf 2) for project items, or the customer is allocated those locations for any of their projects or items are allocated to a project and can be placed anywhere. Your current schema indicates the second one, but don't understand why you have a projectID in the warehouse table and don't see the rationale of including the project table as you have it

I presume the aisles, bays and shelves do not get reconfigured on a regular basis but can vary in size so you might want to include some dimensions for bays and shelves (width and depth for bays, height for shelves) or perhaps use bins. Would help in narrowing down where new items will fit.

 

[jdraw]

I agree with CJ's comment.
Here is a link to a post I made a few years ago in response to a poster dealng with a very similar subject of
Locations, Warehouses,Shelfs, Bins, Material. It may offer some insight.

 

[tmyers]

Some if the fields I may have missed removing when I deleted everything and reworked the tables, the projectID in the warehouse table is likely one of those and I was struggling on where to put project and customer in everything. I think both tables are needed as you have many customers and each customer can have many projects and each project can have many items. As I said, that part of the schema I am struggling with.

I think I follow your description, so I would kind of keep the same tables, just relate them down a chain rather than how I have them now, which makes sense (how did I not think of that?).

Your last assumption is correct in that items are allocated to a project and can be placed anywhere. There are no locations that are set aside for specific projects and the racking does not get reconfigured.

Edit:
With the exception of shelf 1 (the floor), all spaces are the same dimensions (12' wide by 6' deep by 6' tall) but the floor bays are taller (10'). So I would only have to specially identify the floor, but I dont think it would be needed.

 

[The_Doc_Man]

The way I would look at this is to establish the fact that a location can be empty, partly full, or completely full, but because of size issues, you might never recognize that you can't put anything else there. You could have a location full with one industrial-sized commercial restaurant freezer where it is too big for another such freezer; or you could have a stack of floor tiles in boxes, with maybe 400 boxes - but you might be able to add one more layer of the tiles because the boxes are skinny and lie flat. (I'm imagining what I see in Lowe's or Home Depot as the closest example with which I am familiar.)

I see several tables (and some serious design-level thought).

Table: Location - with key such as the compound key Aisle, Post, Level - where you number slots based on which aisle holds them, then you number each post in the aisle, and you have at least a couple of shelves. That amounts to 3 dimensions, usually enough to locate something in space. However, here is where you would include Warehouse ID if ever you needed to do this. And doing so WOULD require a Warehouse table to hold the prime key that would become your warehouse ID. In this table, you would have a designation of the SIZE of the location in, say, cubic feet or cubic centimeters or cubic fathoms, whatever your unit.

Table: Customer - with suitable info about the person. Should be easy.

Table: Project - with suitable contract info (I suppose you do this stuff via contracts)

Table: Items - which I assume are the components used to build your projects. There is a potential source of confusion here, since the question is, if you assembled something for a project, is that assembled something ALSO an item? In any case, you would include the size of the item here as well as the 3-dimensional (4 if warehouses become a factor) coordinates of where the item is located.

Might Not Be A Table: Bins - kind of depends on whether there is some actual identification of the bins. The bin MIGHT be treated simple as a shelf item that contains a huge collection of items all of the same type. Like a bin contains 100 boxes of 3" bolts. It is not clear that you actually NEED a designation for "bin" so much as that in the ITEM table you would have a flag that says "located in a bin" at location 16A3 or something like that. BUT if you need to allow space for the bins, you might treat them as packaging of the item in question. In which case the size of the item includes the size of the bin in which you store it.

Table: Assemblies - since you mentioned "items that make up a project." That "project" nomenclature makes it seem as though you build something sometimes. If so you have to consider that a "built" something itself contains items. Maybe you don't want to track items once they are built - but this table COULD be the guide for what you need to BUILD an assembly. And if you treat an assembly as an item once built, it can have size and location as an assembly.

The stinker in the pile is where you have an assembly so large it occupies multiple locations But the solution is that if your location table is like a JUNCTION table, you just mark the locations with the same assembly or item number and flag the item as "overlapping locations."

When you have sizes, you can now do a "WHERE IS" query of items grouped by aisle, post, and level and sum up the volumes of items in each case. To find an empty location, you do a query with the capacity shown and the occupancy as well - and an empty slot has zero occupancy.

This is just a random bunch of thoughts about how I might approach the problem.

 

[Minty]

I think you need to separate your thinking about your locations from your products.
It's largely immaterial what goes in a location, what you need initially is a robust method of storing the locations.

In your case, that would be a locations table with WarehouseID, Aisles, Bays, Shelves and a unique Location_ID, and possibly an "InUse" flag in case a location becomes unusable or retired for any reason.

You then simply store the Location_ID with your transactional movement records.
Your Location table has all the available locations in it. Your empty location query then simply compares everything with what is showing some stock in it against the rest of the table.

I've written a number of stock/product movement databases and they aren't super simple to get right.
Plan it out on bits of paper, and make sure it can cover all eventualities, including stock checking, and moving things from one location to another.

 

[CJ_London]

Your last assumption is correct in that items are allocated to a project and can be placed anywhere.

let' standardise on what things are called so items?

Sounds like your items could be anything your customers send from nuts and bolts to fridges/freezers so you don't necessarily need an items table but suggest it would be worthwhile even if only to ensure standardised spelling. Other factor is size 25kg bags v 5Kg bags of the same item. Maybe matters, maybe not.

So your relationships would be

customers>>projects
projects>>items
item>>bins
and bins then goes back 'up the line' to determine its physical location of aisle, bay and shelf

since a) items could be spread over more that one bin, b) a bin can contain more that one item at the same time and c) over time the contents of a bin will change to different items, you will need a joining table between items and bins. This table would also include as a minimum an indate, an outdate and a quantity - effectively a transactions history.

You can then determine what bins are empty when the sum of transaction quantity=0

To determine a bins 'state of fullness' would require dimensional information of bins and items.

 

[jdraw]

I think you have to flesh-out the requirements based on the latest advice from Doc, CJ and Minty.
Perhaps you have some sample data and can use it to develop and vet your evolving data model.

 

[LarryE]

Because each customer may have several "projects" (I think you previously called them "Jobs"), which may have multiple materials, each of which could be held in multiple locations, I built this one based on your previous posts and requirements. I don't have individual tables for each location but you can still search and filter by locations. Each location may have multiple customers and materials as I recall. You can see if it meets your needs.

 

[tmyers]

Thank you all for the replies! I am working through them now.
Larry, I apologize to you directly as I had changed computers and moved to the other side of the office since your previous assistance and had completely forgotten about your example. Either way, I am reading/looking through everything now.

Once again thanks everyone! Will report back as soon as I can

 

[tmyers]

Before I get pulled away again, I wanted to reply to Doc and to try and help provide context.

I work for an Electrical Distributor. We sell electrical components from light bulbs, breakers and outlets/switches for homes up to giant 5000A switch boards for industrial facilities and entire lighting packages for anything from commercial buildings to newly constructed hospitals and everything in between. When we are awarded the contract for a job/project (interchangeable terms for us which is why I bounced around without realizing it), most of the time we order all of the power equipment and lighting for the given project all at once.

As the material comes in, we find what customer and project it is for then find a place for it to be stored until the material is released for shipment. In the meantime, we make a label (manually typed up in Word) and tape it to the skid/pallet (which is why I was stuck using that word previously) that has the customer name, project name and all the type designations and quantities that are on that pallet. That information is then entered into an Excel file to keep track of it (see attached example).

The problem is this file is actively used between roughly 8 people and has been corrupted, accidentally overwritten and several other detrimental things multiple times and in the event the file gets messed up, it takes a lot of time to track down material when it is released for shipment. There is also really no way of telling by looking at the sheet what places are open or in use besides going and physically looking.

So back to your response, we wouldn't need to know so much the size of the given space as it is all the same. We also wouldn't mix material from different jobs and we typically don't add to a given location (if we would, we would just update the entry for that given spot but it is rare) so if a location has items in it, we wouldn't need to know how full that specific location was as it wouldn't be added to. We also don't do any assembly as the material comes in either fully assembled from the factory or is to be assembled in the field by the electricians, not us. The only stinker (as you referred to it as ), might be weight but generally monstrously heavy things are left at ground level for obvious reasons.

Hopefully I didn't muddy the water more and make it more convoluted. I will keep reading the other replies and tinkering with Larry's example and will report back!

 

[Pat Hartman]

You still have too many missing pieces. the Job/Project is a red herring when working on the warehouse description. It's like trying to associate my name with a storage location at the nearest Amazon warehouse. OK, I don't order enough from Amazon to warrant my very own bin but one of my friends does

Would the widgets for JobA always be stored in a separate bin from the widgets for jobB, probably not unless you are assembling a "pallet" to be shipped for JobA. Then as the shipment is prepared, the widgets are moved from their natural location to a staging location as the pallet is assembled.

 

[CJ_London]

As the material comes in, we find what customer and project it is for then find a place for it to be stored until the material is released for shipment. In the meantime, we make a label (manually typed up in Word) and tape it to the skid/pallet

Depends how close you want to get to identifying which pallet but sounds like a pallet is a bin - or to put it another way a shelf can hold up to 4? bins/pallets

 

[Slap]

In warehousing you'll often see the term LPN (Licence Plate Number) which refers to a collection of goods stored together and intended to move together. In your instance there could be one or more per job/project.

You'd relate the LPN to the physical location and also to the job and a list of contents.

tblLPN ~ fk to tblJOB
tblLPNContents ~ fk to tblLPN
tblLocContents ~ fk to tblLoc and tblLPN

 

[Pat Hartman]

As the material comes in, we find what customer and project it is for then find a place for it to be stored until the material is released for shipment.

This means you are not running a normal warehouse. Everything that comes in is targeted for a specific job and allocated to that customer so it will NEVER be used for a different job or a different customer. That was a pretty important piece of information that you might want to have mentioned up front in your first thread since it impacts how the warehouse should be organized.

 

[LarryE]

Do you need to filter for unused Aisle and Self locations? Or does that matter as long as each customers material can be located?

 

[tmyers]

Sorry Pat! I did not realize how import that bit was .
Larry, it would be nice to have the ability to see unused locations. I had originally envisioned cascading combos that as you selected an aisle, it would filter down the chain but this has changed so much, I am not sure anymore.

For now this project has been placed on the backburner however.

 

[LarryE]

Sorry Pat! I did not realize how import that bit was .
Larry, it would be nice to have the ability to see unused locations. I had originally envisioned cascading combos that as you selected an aisle, it would filter down the chain but this has changed so much, I am not sure anymore.

For now this project has been placed on the backburner however.

OK