help with relations..
-
hi,
maybe you can help me with the following scenario:
- there are single parts
- there are part-lists that can contain both, single parts and other partlists
- there are locations, parts can be at multiple locations
so far so good... there is a table [Parts], a table [PartLists] and a table [Locations].
between [Parts] and [PartLists] there is a junction-table [PartListsParts].
there is another junction table [PartListsPartLists] for partlists containing other partlists.
between [Parts] and [Locations] there is a junction table [PartsLocation] as parts can be at different locations.
now here comes the tricky point:
- there are reservations
u can make a new reservation for a single part or for a partlist. so i first thought i'll just build 2 junctiontables, one [ReservationsParts] and another [ReservationsPartLists]. but there has to be the information from which location i reserve the certain parts which i dont have when i just link a hole PartList to a Reservation. so i thought i just drop the [ReservationsPartLists]table and instead make a function to insert a new row in [ReservationsParts] for every Part in the PartList including the information from which location it is reserved. but with that method i would lose the information that there was a PartList to be reserved, not a bunch of single Parts. so my final idea was to make the junction table [ReservationsParts] like:
Id
ReservationId NOT NULL
PartId NOT NULL
PartListID NULL
PartsLocationId NOT NULL
Count
so i would keep the PartListId column empty when there is a single part reserverd and insert the PartListId if the certain part comes from a partlist-reservation.
im just wondering if allowing a foreign key column to be null is the best idea or if there might be a completely different and much better approach to my problem as it appears in many more instances all over my DBDesign while handling partlists and single parts.
thx for any suggestions ! (''\(^_^)/'')
-
Based on the information provided I don't see any reason not to have the [PartListId] column in the table and allow it to be null since it is not always applicable. There are a lot of ways to skin this cat and more details would be needed to insure you have an optimal design but based on details provided I think you have the right idea. There are some normalization potential (i.e. the partid should/would be available through the partlocation reference and since that is required the actual "partid" value is not needed in the reservation table). For ease of use/queries I can see why you would want it there but in that case I would make my foreign key reference the "PartsLocations" table with a composit key of PartID and LocationID just to insure I've recorded the correct Part and Part Location (otherwise you could accidentally record a part with a different parts location). Same thing with the PartsList reference, when present it should be a composit of the PartID and the PartListID to insure that when a part list is referenced the part recorded is really part of that list.
James.
-
thx alot... i'll try to get my whole DBDesign to work with this idea and if i should encounter more probs.. be sure i'll post them here
-
Your going about this all the wrong way.
You only need 2 tables and 2 Cross-Reference tables (what you call junctions) in your design.
1.) Parts
Let's start simple
a.) ID (Int or UniqueIdentifier)
b.) ParentID (same data type as PartID
c.) Name
2.) PartLists - this is really an XRef (junction) table which allows the Parts table to do a many-to-many join on itself. This allows you to have a Part entered once which can belong to many lists if necessary.
a.) ListID
b.) PartID
3.) Reservations
a.) ID
b.) Code (ReservationCode)
c.) etc. (Date, blah, blah)
4.) ReservationDetails
a.) ID (UniqueID for the table)
b.) ReservationID
c.) PartID
Ok, what you need to do in order to create a "Parts List" is to add the "List" as an actual Part. It gets a PartID, Name, etc. Next, you add ALL the parts to the table as well. The ParentID can be used when a Part is ALWAYS a child-component of another part. Now, to create a "List", you use the PartLists table. Add the "root" PartID that is the List, and then add any PartIDs that belong to that list.
Now, to "reserve" a Part or a whole list of Parts, you'll use the ReservationDetails table. Let's say I want to reserve an entire List, as well as one additional part that is not on the List. In this case there will be only 2 records in the details.
Example:
(Parts)
1- Engine
2- Camshaft
3- Gear
4- Axle
(PartLists)
Row1 (ListID = 1, PartID = 2)
Row2 (ListID = 1, PartID = 3)
(Reservation)
1 - Date = Today
(ReservationDetails)
Row1 (ReservationID = 1, PartID = 1)
Row1 (ReservationID = 1, PartID = 4)
So, in this example, I am reserving the entire "Engine" as well as the "Axle". Now, there are plenty of example out there on how to write a function in SQL which can give you the heirarchy list of Products, Parts, or whatever, so with this design you'll be fine for Reporting as well as generating Invoices, etc.
Good luck!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply