2

enter image description here![enter image description here][2]I am working on a database that will create a three-tier structure. The idea is going to look like this:

  • 1st level: Order table
    • Primary Key - OrderID
  • 2nd Level: OrderlineItemHeader table
    • Primary Key - OrderID
    • Primary key - OrderLineItemHeaderID
  • 3rd Level : OrderLineItem table
    • PrimaryKey - OrderLineItemHeaderID
    • PrimaryKey - OrderLineItemSequence

The problem is it doesn't let me make the foreign key between the second and third levels (OrderlineItemHeader.OrderLineItemHeaderID to OrderLineItem.OrderLineItemHeaderId). I've been reading about the hierarchyid data type but the problem is that they relate them on the same table. What I'm showing are the main columns, but each table has five to ten columns more of information needed.

I am using SQL Server 2008 and the program that will associate it will be VB.NET 4. I cant add an image of my diagram because I'm new to the website.

Going to Put the Explanation again With the Food Example:

You place a huge Order for a Food Providor who owns all the Restaraunts in the US.

The Order placed has general info for that one huge order list.

OrderID, OrderNO , OrderDate

Now that Order has Many different type of foods, which is the second level.

Pizza , Chicken, Meat, Fish .

You order 1000 of each and you need to know what is coming when.

So they give you that information and you need to show everyone is going to eat when they are going to eat.

So from Pizza youll have 100 coming on tuesday by air. 500 coming by water coming next week. and the rest by land in 2 weeks.

Meat has 500 coming tmorrow, but since they dont have more ready, its backordered for a future date the rest.

So all that info needs to be put in tablkes, hence the 3 tier model

Enrique Padilla
  • 21
  • 5
  • Your requirements do not seem to require a hierarchical model to solve it. This simply looks like a relational solution. What makes you think you require a hierarchy in there? – Raj More Nov 11 '11 at 21:47
  • Maybe we can help better if you explain what you're trying to store. – Raj More Nov 11 '11 at 21:48
  • Orders has many orderheaders and orderheaders has many line items – Enrique Padilla Nov 11 '11 at 21:49
  • Is there a way to attach a print screen so i may show the diagram that i have ? I just joined the website looking for help and i remember this is actually a place i normally got my answers for easier problems – Enrique Padilla Nov 11 '11 at 21:51
  • What about the items that in the order.. where are they stored? – Raj More Nov 11 '11 at 21:54
  • images: http://blog.stackoverflow.com/2010/08/new-image-upload-support/ – Raj More Nov 11 '11 at 21:56
  • Order has general information, orderid , orderno, etc – Enrique Padilla Nov 11 '11 at 21:57
  • 1
    Why - how - does an Order have many Order Headers? The industry standard is that an Order has *one* Header with many Order Lines. That is, an Order and an Order Header are synonymous. – APC Nov 11 '11 at 21:57
  • ORderLineItem would be, they send you 5 boxes of pizza by car, and another one would be 5 boxes of pizza by motorcycle – Enrique Padilla Nov 11 '11 at 21:58
  • Looks like the OrderHeader is something that can be calculated and displayed at run time. No need to store it. – Raj More Nov 11 '11 at 21:59
  • Where do you store the details of the Pizza toppings? – Raj More Nov 11 '11 at 22:00
  • You are confusing shipping - satisfying an order - with the process of ordering. An Order is: I want some food now. My Order Header is my address, my phone number and my favoured customer number. My three Order Lines are: (1) 2*Garlic Bread (2) 3*Margerita Pizza (3) 1*Side Salad. The Shipping Details are rush me the Garlic Bread and one Pizza by jet-pack, send the other pizzas by moped and don't worry about the salad: "whenever" is fine. – APC Nov 11 '11 at 22:04
  • I think i need to change the titles: MainOrderHeader OrderLineItemHEader OrderLINeITems – Enrique Padilla Nov 11 '11 at 22:04
  • I think you're right in changing the names – Raj More Nov 11 '11 at 22:05
  • Getting the Entity titles right is half the battle. – APC Nov 11 '11 at 22:05
  • jaja i still need 10 points of reputatio nto upload the image, and yea im confusing you guys with the titles – Enrique Padilla Nov 11 '11 at 22:08
  • I do appreciate the help btw , and its just hard to explain without images :( – Enrique Padilla Nov 11 '11 at 22:08
  • Although your revised suggestions still sound odd. In fact they sound "enterprise-y": http://projects.haykranen.nl/java/ – APC Nov 11 '11 at 22:08
  • Ok , the problem with your idea of only pizza is that u ask for the food from a huge food provider, they send you the pizza from pizza hut, they send you the chicken from kfc, and they send you the meat from outback – Enrique Padilla Nov 11 '11 at 22:09
  • jajaja its not a food project, im trying to build a program for a company , long story , 19 years old :p – Enrique Padilla Nov 11 '11 at 22:09
  • and beside of the 3 different places they send you in different packages in diferent dates, so i need all tha tinformation so people who are going to eat understand what is coming when – Enrique Padilla Nov 11 '11 at 22:11
  • So you have something like a Master-Order and Store-Order (whatever names you choose), and then for each Store order, you have a list of Items and the Delivery method. – Raj More Nov 11 '11 at 22:14
  • YESSS, thats the word , Well you have Master order , That has different products, But those products get sub categorized on how they get shipped . Uploaded the diagram , see Pedido = Order . and well PLID is orderlineitem id – Enrique Padilla Nov 11 '11 at 22:19

2 Answers2

1

There is a lot of additional work that goes into this, but here is a basic concept based on our discussion so far:

enter image description here

EDIT

Second Diagram based on discussion (Keeping the older one for history)

enter image description here

Raj More
  • 47,048
  • 33
  • 131
  • 198
  • It does not allow me to relate the second level , to the third level, are the primary keys wrong? Thats what i thought , but ive spent an hour on trying to relate the tables – Enrique Padilla Nov 11 '11 at 21:47
  • Ok i really appreciate this and love to give you points recomendation whatever :p , So yes your more or less on the right track, but now we need the 3rd level . On the StoreOrderItems, Imagine they need to be broken up since they wont be shipped all at the same time, they are fragmented. – Enrique Padilla Nov 11 '11 at 22:24
  • Yeah.. I will add another diagram – Raj More Nov 11 '11 at 22:28
  • let me add you the 4 main tables for you to see what im doing exactly – Enrique Padilla Nov 11 '11 at 22:31
  • Let me understand.. Customer can order 20 Pizza from PizzaPalace - 10 for today, 10 for tomrrow - these are all in the same order, same invoice, but separate deliveries? – Raj More Nov 11 '11 at 22:32
  • yes , doesnt let me post, ohhh theres a minimun for a comment ohhhhhhh – Enrique Padilla Nov 11 '11 at 22:37
  • ok thing is the storeorderdeliverydetails is wrong, they go under storeorderitems, each item is separated, the 10 pizzas for today have their own tracking nomber, pizza palace doesnt send all 20 pizzas with the same information – Enrique Padilla Nov 11 '11 at 22:39
  • So in that case why can't you have two StoreOrders with separate list of items and then track them individually? – Raj More Nov 11 '11 at 22:41
  • Because the order is split up in n times, i dont know how many of the same product will be sent in different tracking numbers – Enrique Padilla Nov 11 '11 at 22:43
  • I need to connect PizzaPalace, Kfc , Mcdonalds, to all have on the same table their orders ( they use the same information) , and remember i can order from each restaurant at the same palce – Enrique Padilla Nov 11 '11 at 22:44
  • i'm trying to take this to chat so we can talk faster, but you don't have enough reputation yet for chat. – Raj More Nov 11 '11 at 22:46
  • Since i had two tiers before, i had orderlineitems with two primary keys, now i left it with one, and it was able to relate one to many to the new third table – Enrique Padilla Nov 11 '11 at 22:46
  • I think you need to normalize your structure further. – Raj More Nov 11 '11 at 22:48
  • i know, it sucks beeing a new member, i tried al of that before :/ , i think i got it to work , im going to do some queries too se if they relate correctly , ill post back here on monday with my results, i deeply appreciate all your help , and hopefulyl it works out :) – Enrique Padilla Nov 11 '11 at 22:49
  • its missing alot of tables , the database is going through a huge facelift , hence the needing of help :) – Enrique Padilla Nov 11 '11 at 22:50
0

In your original problem statement you have:

3rd Level : OrderLineItem table
PrimaryKey - OrderLineItemHeaderID
PrimaryKey - OrderLineItemSequence

in order to create a foreign key to OrderLineHeader, it's complete PK must be included, so you need:

3rd Level : OrderLineItem table
PrimaryKey - OrderID
PrimaryKey - OrderLineItemHeaderID
PrimaryKey - OrderLineItemSequence

but if all the IDs are artificial than a better solution may be:

1st level: Order table
Primary Key - OrderID

2nd Level: OrderlineItemHeader table
Primary Key - OrderLineItemHeaderID
Foreign Key - OrderID

3rd Level : OrderLineItem table
Primary Key - OrderLineItemID
Foreign Key - OrderLineItemHeaderID
sceaj
  • 1,573
  • 3
  • 12
  • 24