5 Replies - 3735 Views - Last Post: 10 October 2012 - 03:48 PM

#1 Taledus  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 05-October 12

OMS Relationship Problems

Posted 08 October 2012 - 09:44 AM

Hello all,

This is a side project I have been working on/off with for quite a while just to better my DB skills. I have re-worked this thing about 20 times trying to get this from different angles but I just am not understanding the proper way to make this work, and am reaching out for some guidance on what I am doing wrong as the table relationships do not appear correct, as when creating an order I have been unable to select certain things that are required. This is the back-end to a VB.NET project I am working on.

I am attaching the Access 2007 file and will try to best explain what I am trying to achieve. I really only need help understanding how to get the relationships correct, not someone to fix it.

Here is what I am understanding:

A CUSTOMER has many ORDERS

An ORDER can consist of unlimited WIDGETS - 1 minimum

An ORDER can also have unlimited ORDEREXTRAS - optional

An ORDER can also have unlimited RENTALITEMS - optional

An ORDER can be delivered to the CUSTOMER address or another DELIVERY address - optional



A WIDGET can consist of unlimited ITEMS - 1 minimum

A WIDGET can also have unlimited WIDGETEXTRAS - optional



An ITEM is created with 1 MOLD and 3 CONSTRUCTORS - 1 total for each

An ITEM can also have unlimited ITEMEXTRAS - optional

A MOLDCATEGORY has many MOLDS


Run-through:

When I am creating an ORDER I select unlimited WIDGETS, unlimited ORDEREXTRAS, unlimited RENTALITEMS, and if the order will have DELIVERY

When I am creating a WIDGET for the selected order I can select unlimited ITEMS and unlimited WIDGETEXTRAS

When I am creating an ITEM for the selected widget I can select unlimited ITEMEXTRAS, but the ITEM must have a MOLD that defines the dimensions, and also must have 3 CONSTRUCTORS that define certain attributes of the ITEM

Note: An ITEM cannot be put directly into an ORDER, it must be put into a WIDGET first then the WIDGET be put in the ORDER


I was thinking that this would be done similar to a regular OMS database where you have an order that consists of products that consists of more products, but I was wrong. In trying to create my lookup tables I have added unique ID's for them in order to link the optional extras lookup tables into that have no unique ID's, and I am thinking that this is the wrong way, yet cannot figure out any other way to do it, as each area has priced details that can consist of more priced details along with them.

At any rate, that is what I am struggling with. I would really appreciate some guidance on getting this to work properly. If you have made it this far, thanks for reading :)


Attached File  WidgetData.zip (92.17K)
Number of downloads: 35

Is This A Good Question/Topic? 0
  • +

Replies To: OMS Relationship Problems

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: OMS Relationship Problems

Posted 08 October 2012 - 12:29 PM

I am guessing by description in you post that OMS has something to do with manufacturing.

Review links referenced in this thread http://www.accessfor...join-28334.html
Was This Post Helpful? 1
  • +
  • -

#3 Taledus  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 05-October 12

Re: OMS Relationship Problems

Posted 09 October 2012 - 11:43 AM

Thanks for the link, but my problem is still unclear.

I have worked this Db file around again, here is where I am having issues at:

[tb]ORDER
[pk]Order_ID (one)


[tb]ORDER_WIDGETS
[pk]OrderWidget_ID
[fk]Order_ID (many)
[fk]Widget_ID (many)


[tb]WIDGETS
[pk]Widget_ID (one)


[tb]WIDGET_ITEMS
[pk]WidgetItem_ID
[fk]Widget_ID (many)
[fk]Item_ID (many)


[tb]ITEMS
[pk]Item_ID (one)


On my relationships, my ORDER_WIDGETS table has the (many) sign on Order_ID & Widget_ID...I cannot figure out how to get it with a (many) sign on Order_ID & a (one) sign on Widget_ID, & the (many) sign from that going to Widget_ID

[tb]ORDER
[pk]Order_ID (one)


[tb]ORDER_WIDGETS
[pk]OrderWidget_ID
[fk]Order_ID (many)
[fk]Widget_ID (one)


[tb]WIDGETS
[pk]Widget_ID (many)


[tb]WIDGET_ITEMS
[pk]WidgetItem_ID
[fk]Widget_ID (many)
[fk]Item_ID (one)


[tb]ITEMS
[pk]Item_ID (many)

Essentially I am unable to show the relationships where an ORDER has many WIDGETS, and a WIDGET has many ITEMS...this way when I pop the tables up on my form I will be able to have everything display properly.
Was This Post Helpful? 0
  • +
  • -

#4 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: OMS Relationship Problems

Posted 09 October 2012 - 05:36 PM

I might not be very helpful since I have never built a manufacturing type db. However, regardless of your confusion on the one-to-many indicators, your latest structure looks fine to me.

What do you mean by "pop the tables up on my form"? You will use subforms and maybe even subsubforms?
Was This Post Helpful? 1
  • +
  • -

#5 Taledus  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 05-October 12

Re: OMS Relationship Problems

Posted 10 October 2012 - 06:13 AM

I am going to up the rep here for taking the time to try and help, but I believe I have presented an unsolvable problem.

Normally I would just have an ORDER consisting of PRODUCTS(WIDGETS), and I select the PRODUCTS for the order, get the price and I'm done. But my PRODUCT has to be built first with ITEMS and a controlled set of attributes, which they too have to be built based off of a controlled set of attributes.

On my ORDER I am trying to add WIDGETS, that do not exist because they have no ITEMS that construct their attributes. The ITEMS must be made first, then create and select the WIDGET they belong to, then create the ORDER that the WIDGET belongs to.

Initially I did not think this was a manufacturing database, but I guess it could be considered that based off of what I have presented that I am trying to build here (widgets/items), which are really inconsequential as it is the relationships that is the main problem, not the 'stuff' it is used for.
Was This Post Helpful? 0
  • +
  • -

#6 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: OMS Relationship Problems

Posted 10 October 2012 - 03:48 PM

Rep not important.

Regardless of what 'widgets' and 'items' really are, this structure has the flavor of a manufacturing db. I think manufacturing is one of the more difficult db structures.

Whether or not you can select a widget (with or without related items) for an order depends on if referential integrity is set up. You can even set up a db without any established relationships. I have one db that I cannot include all child tables in relationship to parent table because the number exceeds limit allowed. I control data integrity with code and users do not work directly with tables and queries.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1