November 25, 2009 at 8:51 am
Hi,
I have three tables:
- Container
- Item
- ContainerItem
ContainerItem holds which items are in which containers.
What I want to do is have five tables:
- Container
- Packet
- ContainerPacket
- Item
- PacketItem
ContainerItem table will not exist in the new model. Items are now in packets and packets are in containters.
I have included two pictures in the post showing the diagrams of each model.
When complete, any packet can have one or more items but a given item can only be in one packet.
Any thoughts?
Thanks in advance,
Peter
November 25, 2009 at 9:52 am
I don't see anything in your design that limits an item to one packet. You would need a unique constraint on ItemID in the PacketItems table in order to enforce that. This will enforce the rule that an item can only be in one packet. Your current design of a PK on PacketID, ItemID just enforces that that an item can't be included twice in the same packet.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 25, 2009 at 9:56 am
You are correct. Assuming that the constraint will be there, any thoughts on how to approach the problem?
November 25, 2009 at 10:09 am
I'm not sure what the problem is, so I can't really offer advice. Do you mean moving the data from the 3 table design to the 5 table design?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 25, 2009 at 10:10 am
Yes, exactly. Assuming the constraint mentioned in your first post is in place.
November 25, 2009 at 11:21 am
Well first you have to create the packets and the container_packets. Then you can use the container and container packets to migrate the packet_items. I'm assuming you are keeping the same container id's and item id's and that the packet id's don't really matter. So once you packets and container_packets you could do a query like:
Insert Into packet_items
Select
CI.item_id,
CP.packet_id
From
originaldb.dbo.container_items CI Join
newdb.dbo.container_packets CP ON
CI.container_id = CI.container_id
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 25, 2009 at 11:49 am
Thank you for all the replies. Your code will work once the packets and container packets are created as you say. However, I think the difficult part is creating the packets.
Currently, items are in containers. In the end, those same items will be in the same containers except they will be wrapped in packets. I gather you have seen this based on your comments.
Since an item can only be in one packet and the items need to be associated to the same containers (albiet through a packet), I need to derive what the packets should be from data in the ContainterItem table.
That is really the crux of my problem.
Ideas?
November 25, 2009 at 12:01 pm
Do the packets have actual identifiers? Are the items already in the packets? How does the actual, physical process work? That affects how you have to do it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 25, 2009 at 12:20 pm
The only thing that does exist is the three table model (Container, Item, ContainerItem).
Packet, PacketItem and ContainerPacket tables do not yet exist. My plan for the primary key for the Packet table is an identity integer. However, it doesn't really matter in my situation. It could be any unique identifier. There is no natural key.
I am unsure what you want to know when you say "actual process". Here's a stab at what I think you may mean:
Essentially, I need to derive the packets based on the ContainerItem table data. This would entail finding the all the unique subsets of items per container. An item can only be in one subset. These will be the packets. Once I have this, need to be able to populate the PacketItem and ContainerPacket tables.
Let me know what additional information you need.
Thanks
November 25, 2009 at 12:36 pm
I am unsure what you want to know when you say "actual process".
I mean the physical process where a items is picked, put into a packet, and the packets are put into a container. If there are no physical identifiers for the items, packets, and containers how do the people packing them know that they are doing the right thing?
This would entail finding the all the unique subsets of items per container. An item can only be in one subset. These will be the packets.
And how do you define a subset using the existing model? The only columns you are showing in the images are id and name columns. With that information there is no way to determine subsets, unless the subsets are based on a substring of the item name.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 26, 2009 at 8:43 am
The entities I have described in this post are just examples to illustrate the problem. In reality there are no containers, items or packets.
I'll continue to use those entity names to provide greater understanding of the context. A system exists with something more or less equivalent to containers, packets and items. The packets are pre-defined in that system. That system is used to generate information for another system.
We want to add new containers. Packets are purely internal. People with business knowledge have no knowledge of packets. They see containers with items. In order to add new containers, business folks tell me what items (new or old) should be in what new containers. Then manually, new packets need to be defined based on an analysis of that information. Business people change their minds as to which item belongs in which container. The manual work then needs to be redone. The manual bit is the part I would like to get rid of.
Hopefully that helps, but at the end of the day the problem is the same.
Here's what I was originally thinking to solve it:
- I have the intersection table ContainerItem
- I loop through every distinct Item in ContainerItem
- In the Item loop I create a group that holds all the containers that have a reference to that Item.
- I also keep track of which Items are in which group
At this point I would have a group of groups. The following is true of the groups:
1) There is one item in every group
2) There is one or more containers in every group
There are two possible problems:
1) There could be duplicate groups (groups with the identical containers and same number of containers)
2) Subsets could exist within larger groups that have the identical containers as smaller groups.
Here is a way to tackle the first issue:
- Loop through all the groups.
- Compare all groups that contain the same number of containers.
- If any two groups have the same number of containers and identical container ids then they are the same. Merge the two groups.
- In merging, the item from one group will be moved into the other and that group will be deleted. Every container that has a reference to the group that is to be deleted needs to be updated to have a reference to the group that is remaining.
- The loop stops when no two groups contain the same number of containers
Duplicate groups are now gone. I am continuing to work on the second problem.
When it’s done, no item will be in more than one group and all groups will be associated to the proper containers. The groups are now the packets that we have been discussing.
Needless to say this is a bit of a looping nightmare. What I am really looking for is a far nicer, set-based way of achieving the same thing.
I did not want to go into more detail earlier. I did not want to bias someone else's thought process in the hope they might have a simpler way of approaching this.
Thank you for your help so far.
Any thoughts?
November 26, 2009 at 10:45 am
I think you may have adequately conveyed the gist of what you want to do, however, it'd be easier for many of us to understand if you would work up some sample data in those proposed tables.
Use enough sample data to cover the situations you described in the post above, and then give results for that set of sample data after it has been moved to the appropriate packets. We'll also need the rules for deciding what containers are assigned to which packet, which items are moved around / combined etc. etc. The whole thing is really just too vague and undefined to write code for at the moment.
Helping you get from A to B is fine, but here you're really trying to go from A-F. There are a lot of steps still before we can help you write non looping code.
So, we'll need:
Table create statements with sample data for all the tables
Self loading sample data to populate those tables and that covers as many different situations as you can
Rules for how that data should be moved around / grouped
What the final results should be after all the rules have been applied.
It sounds like a lot of work (and to be honest, it is), but most of it you'll need to do anyways, and it forces you plan out all of those rules and put them down on paper and refine them all etc. beforehand, which it doesn't sound like you've fully accomplished yet.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply