Table design/relationship question 1

[TCARPENTER]

I have 5 tables, Location, Rack, Container, Part, and LocationDetails. I have done Bill of Material databases in the past and thought this was pretty much the same, however, instead of just 2 tables as in a BOM with parent child relations, I cannot consolidate the tables any further (they would be huge) and my dilemma is this:
Location can only be a parent to all others,
Part will never be a parent to any others,
Container can never be a parent to Rack,
Rack, Container, and Part can be in Location independent of each other.

Setting up a BOM I would just use two tables, one for just the basic components and one to hold the relationships - but I'm stumped trying to apply this same logic this problem.

Any insight would be greatly appreciated.
Thanks
Todd

 

[lespaul]

what are you doing with this database? What are locations, racks, parts and containers? If a container can't be a parent to a rack, can a rack be a parent to a container? How do all these pieces fit together?

 

[willir]

Is this an order entry / pick list type of database? i.e. tell the order clerk what location to find a part, and where packers and shippers need to go within a location to get a part? Perhaps a database for building a product?

Next, if you are having problems figuring out the relations, it may mean that you have amany to many relation, or a complex relation where a parent can have more than one type of child relation.

I agree with lespaul in that it would be helpful to us if you could provide more details, it would give us an idea on which direction to go in providing advice.

Richard
p.s. Leslie, one of my sons rock climbs, and I knew you like to as well. But I do like to hike and take in the scenary.

 

[TCARPENTER]

Thanks for the reply guys, sorry I wasn't clearer - I have a tendancy to be a little long winded.

The database is actually for a manufacturing floor, tracking moves along with all the equipment and parts involved with either setting up a new station or moving an existing one. The tables in question are only a small part of the whole database.

The part information isn't always used, the container information isn't always used. In short, a location (or workstation) will hold a rack, which may contain containers of parts (like a plastic box, 15x15x15 containing nuts, bolts, washers etc.) Other times the parts are simply too big to have on a rack, and sit by themselves on the floor. Some large parts have a container when they arrive, others don't. Sometimes the part sits on a rack with no container. Sometimes the racks are used for empty containers to return so they don't really have anything associated with them as they are used as needed (sometimes for garbage, overflow of parts etc.)

Since some of this information is optional - depending on what's being moved, who's in charge of the move, it really depends on the need/preference of the person in charge as to how much or how little the database is used.

Hope this helps if not, I'll try adding some screen shots, sample data etc. I'll check back in later tonight, as I'm off to take the kids to an Archeological dig at UofM.

Thanks again for the help, I've been pulling my hair out for about three weeks over this, and actually thought I had it figured out untill I ran a report that after ten-some-odd thousand pages finally crashed Access.

Todd

 

[willir]

Todd

Good stuff Todd.

I see two solutions for this, and your final design will probably depend on how you define the location.

PartTbl
PartID or PartNo - primary key, autonumber or text
PartName

LocationTbl
LocationID - primary key
LocationDesc

RackTbl
RackID
RackDesc

ContainerTbl
ContainerID
ContainerDesc

Farily straight forward to this point.

Now it is desicion time...

If a container or rack is tied to a specific loation, or does not move much, then one solutin is to tie the location to the rack or container. Example...

RackTbl
RackID
RackDesc
LocationID

ContainerTbl
ContainerID
ContainerDesc
LocationID

To find a part, I have set up a many-to-many relationship

PartLocationTbl
PartID
LocationID

I used a M:M relatinship because I suspect it is possible to have parts at different locations. (No more space here, move some over there)

The primary key is the PartID + LocationID


If a location can have a mix of racks, containers and parts without rack / container, then you have to use a complex joining table.

PartLocationTbl
PartID
LocationID
RackID
ContainerID

Setting the primary key is tricky. PartID + LocationID would work if a part is going to be in only one situation - in a rack or container or on the floor. Or if you don't care which part you grab from the locaiton.

Probably a better solution is to make the primary key as
PartID + LocationID + RackID + ContainerID

Problem is that Access will not let you use a null value in a primary key. The work-around solution is to define a rack as "No Rack", and a container as "No Container".

The problem here is that each part has to be uniquely identified. This means you only want to identify important parts this way.

If you are going to track all parts, major and minor, then you have to remove the unique identiiers for the PartLocationTbl

PartLocationTbl
PartLocationID - primary key
PartID
LocationID
RackID
ContainerID

I have given you two types if solutions with variations. Hope it gives you some direction.

Richard

 

[lespaul]

Also check out 'The Fundamentals of Relational Database Design'.


Leslie

 

[TCARPENTER]

Richard,

Thanks for the well thought out reply, believe it or not, this is actually the solution I have in place. The problem I've had though has been giving the entries some sort of relationship to each other - in other words, how do I know a part belongs on a particular rack, or a container holds a particular part? They all seem to have a location, but no association to one another if and when needed.

I was wondering, since a location can have a mix of racks, containers and parts without rack/container, do I just need to add another table, something like:

[tt]tblLocationDetails tblBldg
BldgID------------------------BldgID
LocationID-----------------+ BldgDescr
LocationDetails_Parent---+ |
LocationDetails_Child--+ | | tblLocation
| | +--LocationID
| | LocationDescr
| |
| | tblRack
+-+--->RackID
| | RackDescr
| |
| | tblContainer
+-+--->ContainerID
| ContainerDesc
|
| tblPart
+----->tblPartID
tblPartDesc[/tt]
But I'm not sure if I'm way off base, and if I'm not, maybe this is a form/report structure problem?

Thanks again for the well thought out reply.
Todd

 

[TCARPENTER]

Leslie,

Thanks, I have read this, but since I'm really a CAD jockey/develper by trade, the complexity of this one has me stumped, maybe I'm over complicating things?

Todd

 

[lespaul]

So, from what I understand from your posts.

You have work stations (tblLocations) set up that are in a particular place (tblBuilding). Each workstation has a rack. Each workstation has multiple parts that are assiocated with it. These parts can be on the rack, on the floor or in a container.

You want to be able to make sure that all the parts that are "assigned" to the workstation show up after the workstation has been moved?

Leslie

 

[TCARPENTER]

Pretty much...

- A workstation may or may not have a rack.
- A workstation may have 0 to many parts associated with it.
- Parts can be on a rack, on the floor, in a container on the floor, or in a container on a rack.

Users have requested to either; track parts or not track parts; track containers or not track containers; track some parts; track some containers; and have the ability to move any rack, container, or part and have all pertinent information (rack/container/special tooling/common tooling) move with it. Some programs want to track a lot of detail, other don't. The hope was to use as much of, as little of the database as a particular program wanted/needed (depending on budget/manpower/expertise etc). The problem seems to have reared its head with all the optional portions of the data entry.

Hope this made it clearer - if not, please let me know and I'll try again.

Thanks
Todd

 

[lespaul]

so then I would set up:

tblWorkstation
WorkstationID
RackID (FK - not required)


tblParts
PartID
PartName

tblPartLocation
WorkStationID
PartID
OnRack (Logical T/F)
InContainer (Logical T/F)
OnFloor (Logical T/F)

Now, depending on how much moving around you are doing, for me, would determine how to set up the move tracking of parts and workstations.

HTH

Leslie

 

[TCARPENTER]

How would you create either a report or form, to either show or collect the relationships between the racks, containers, and parts within a location - in other words, how would I know, which rack contains which parts, containers, which containers contain which parts etc., since a location can have a mix of racks, containers and parts without a rack/container?

Thanks
Todd

 

[lespaul]

Once all the table structure is finalized, a query like:


SELECT WORKSTATIONID, RACKID, PARTID, ONRACK, INCONTAINER, ONFLOOR FROM TBLPARTLOCATION LEFT JOIN TBLWORKSTATION ON TBLPARTLOCATION.WORKSTATIONID = TBLWORKSTATION.WORKSTATIONID
ORDER BY WORKSTATIONID

this would give you something like

WSID RackID PartID OnRack InContainer OnFloor

1 1 1 T F F
1 1 2 F T T
1 1 3 F F T
2 2 F F T
2 3 F T T
3 2 1 T T F


So this says that:

Workstation #1 has Rack#1 and:
Part#1 in on the rack, not in a container and not on the floor
Part#2 not on the rack, in a container, and on the floor
Part#3 not on the rack, not in a container and on the floor

Workstation#2 does not have a rack and:
Part#2 not on a rack, not in a container and on the floor
Part#3 not on a rack, in a container and on the floor

Workstation#3 has rack#2 and:
Part#1 on the rack, in a container and not on the floor

If your containers are identified then in the PartsLocation table add a field for containerID to find which parts are in which containers.

You are falling into the "how am I going to enter the data" trap. First decide what data you are going to capture and how you want to store it. Once your tables are designed properly, the data entry won't be that difficult. Most of the question you are asking about how am I going to..... will be accomplished by creating SQL statements that bring back the records you want. If your tables are structured in 3NF, then the SQL to extract the information you need won't be difficult to build.

However, you will have to incorporate some validity testing in your data entry form. You don't want the user to be able to check 'On Rack' if there is no rack assigned to that workstation. If the user has selected 'On Rack' then the choice 'On Floor' should not be accessible for the user to change until they have unchecked 'On Rack'.

HTH



Leslie

 

[TCARPENTER]

Ahhh... The light bulb is coming on thank you very much!

Todd

 

[willir]

Great post Leslie