Database design question

[cismajor97]

This is the scenario. I'm creating an inventoy database for the schools that I am assigned to. The inventory on paper works like this. There is the county level, the region level and the individual school level. In this case there are 8 schools with 1 of the 8 schools labeled as the main school for that region, we call it the PAU. When an order for "widgets" is placed (in this case we will use 8), it comes from the PAU location and it is sent to the County office. The County receives the order that is sent to the main warehouse and the 8 widgets are delivered to the PAU location for that region. Which in turn will be sent to the 7 other sites in that region.

Now here is the tricky part, how would I design my database table to display the PAU site received the 8 widgets and then in turn the widgets have been distributed to their respective school sites?

So far my purchase order database table looks like this:

PO Table
---------------------
PO ID - PK
Pau ID - FK
Name
Item ID - FK
VendorID - FK


PAU Table
---------------------
PAU ID - PK
Name

Vendor Table
----------------------
Vendor ID - PK
Name
Address
etc

Items Table
-----------------------
Items ID - PK
Price
Qty
Unit
Extended Price

 

[mikeblas]

This is the scenario. I'm creating an inventoy database for the schools that I am assigned to. The inventory on paper works like this. There is the county level, the region level and the individual school level. In this case there are 8 schools with 1 of the 8 schools labeled as the main school for that region, we call it the PAU. When an order for "widgets" is placed (in this case we will use 8), it comes from the PAU location and it is sent to the County office. The County receives the order that is sent to the main warehouse and the 8 widgets are delivered to the PAU location for that region. Which in turn will be sent to the 7 other sites in that region.

Maybe I'm tired, but I find your description to be completely opaque. I can't guess what entities you'll want, how you're tracking them, what relationships they'll have, and so on.

 

[cismajor97]

Maybe I'm tired, but I find your description to be completely opaque. I can't guess what entities you'll want, how you're tracking them, what relationships they'll have, and so on.

What I want is the ability to track a group of items that are ordered and sent in bulk to the PAU site, and then the ability to send out the items to the sub schools.

Example:
Lets say School Site A has ordered 8 computers. Now in the database I need to track that School Site A has received 8 computers and then I need to track that 7 computers were sent to School Sites B-H.

 

[mikeblas]

I'm still confused. Are you tracking the shipments, or the objects? Or the receipt of the objects? Or the sending of the objects? Is seven computers going to schools B-H one record (for one shipment) or seven records (for seven shipments) or 14 records (for seven shipments, and seven receipts), or 21 records (for seven shipments, and seven receipts, and seven ownerships)?

 

[Zwitterion]

I'm kind of confused as well. Would making a new column in the table and then flagging it when an order is recieved which moves the data to a data warehouse be achieving what oyu want?

 

[ameoba]

This is the scenario. I'm creating an inventoy database for the schools that I am assigned to.

When you say "assigned" do we mean that this is a homework project or is it something you need to do for work?

If it's something you need to do for work, you're probably better off, time-wise, going out and finding an open-source inventory system then writing one from scratch - there's already plenty of them out there.

 

[cismajor97]

I'm still confused. Are you tracking the shipments, or the objects? Or the receipt of the objects? Or the sending of the objects? Is seven computers going to schools B-H one record (for one shipment) or seven records (for seven shipments) or 14 records (for seven shipments, and seven receipts), or 21 records (for seven shipments, and seven receipts, and seven ownerships)?

I would like to track the objects. So in this case I would want to turn

 

[cismajor97]

I'm still confused. Are you tracking the shipments, or the objects? Or the receipt of the objects? Or the sending of the objects? Is seven computers going to schools B-H one record (for one shipment) or seven records (for seven shipments) or 14 records (for seven shipments, and seven receipts), or 21 records (for seven shipments, and seven receipts, and seven ownerships)?

I would like to track the objects. So the shipment of 8 computers from the Warehouse to School Site A looks like this on our purchase order:

Item Description Price Qty Unit Extended Price
----------------------------------------------------------------------------------------------
1 Gateway PCs $2000 8 each $16000

These 8 computers are all delievered to the main site, School Site A.

My job is to take the 7 computers (1 remaining at the main school site) and deliver them to School Site B-H. I need to track that in my database so when the Principal says how many Gateway PC's have we ordered this year, I can say we've ordered 8 and there exact locations. Then the Principal might say we are closing School Site H so move that 1 Gateway PC to School Site C.

I did some reading last night and think what might have to happen is when the purchase order is entered into the database the user has to enter 8 seperate PC's instead of the 8 PC's as 1 object.

I like the idea of 21 records as that makes more sense of what I'm trying to accomplish. This isn't a homework assignment, this is a project that I've taken on to organize the PC inventory that I'm working with. In a sense I want my data to make sense to someone at the County level who just sees the 8 computers as 1 shipement, and to the Principal at the main site who sees the 8 computers as 8 shipments.

Let me know if this still doesn't make sense.

 

[generelz]

From the sounds of it, you will want four entities:

-Computer
-School
-Purchase Order
-Vendor

Each is related in the following ways:

A computer has a many-to-one relationship with a purchase order (a purchase order contains multiple computers). A computer has a many-to-one relationship with a school (a school has multiple computers). A vendor will have a one-to-many relationship with a purchase order (multiple purchases are made from the same vendor)

Each computer would therefore only have two foreign key relationships - a "school" foreign key and a "purchase order" foreign key (possibly immutable).

The purchase order would have a foreign key relationship to the vendor (possibly immutable).

 

[cismajor97]

Thanks for the help people.

I think my confusion was how the data on the purchase order was going to be entered into the database and the simple solution right now would be that each unit will have to be enterted in as a seperate object.

Later on using C#, I'll figure out a way to enter a batch of computers through the UI. My problem was that I was joining the UI process with the database layout.

On a side note, I found a free data modeler program that helps me organize this information better.

http://www.casestudio.com/enu/default.aspx