G
Guest
I want to develop a database for a shoe factory. In this database I what to
receive orders from customers and based on those orders make shoes to fill
those orders. I have two departments: Sewing (Componets table), and Molding
(FinishShoe table). Once the orders are entered I want the Sewing department
to have a report of how many Uppers of each size and style shoe they need to
sew for each customer's order and in this report I want a running balance of
what is left of the order for their department to produce. I want the same
report for the Molding department. So far I have 8 tables and I have followed
Codd's rule as I understand it (each table has a pk and the foreign key is
only in one table as to not duplicate data). What I want is my tables
reviewed and some suggestions to make them better as I tend to over think
some things and totally miss others.
The Customers table and Shippers table hold just what they say, information
only related to those two subjects. Those two tables relate to the Orders
table via foreign keys in the Orders table. The Orders table is basic and
relates to the Order Details table. The Order Details table relates to the
products table. Up to here I feel pretty confident because I used the tables
directly from the Northwind sample database. Now where I get confused is
adding the ShoeCatagories table (Three distinct shoe styles), Componets table
(different sizes of Uppers for the three distinct shoe styles) and
FinishedShoe table (different sizes of shoes for the three distinct styles).
One of the things I am confused about is do I need to have two fields in
each of the two above tables (Componets and FinishedShoe) to show incomming
and outgoing shoe production for each table or can I show one numeric field
in each table, subtracting or adding one table totals from the other to
obtain production and inventory levels in each part of production the table
represents? A couple of examples are as follows:
Componets needed to be made (Uppers) would be represented by the "Quantiy"
in table "Order Detail", minus how many Uppers were made in table
"Componets", field "ComponetsInStock" equal the amount needed to be made for
that particular order?
If so then I would have to do a similar calculation for each report i.e. the
"Quantiy" in table "Order Detail", minus "FinishedShoe" table
"FinishedShoeUnits" which represent completed shoes equal the amount of units
needed to be molded/finished to complete the customer's order in table "Order
Details".
So this post has two requests/questions:
1) What I want is my tables reviewed and some suggestions to make them
better as I tend to over think some things and totally miss others.
2) Do I need to have two fields in each of the two above tables (Componets
and FinishedShoe) to show incomming and outgoing shoe production for each
table or can I show one numeric field in each table, subtracting or adding
one table totals from the other to obtain production and inventory levels in
each part of production the table represents?
receive orders from customers and based on those orders make shoes to fill
those orders. I have two departments: Sewing (Componets table), and Molding
(FinishShoe table). Once the orders are entered I want the Sewing department
to have a report of how many Uppers of each size and style shoe they need to
sew for each customer's order and in this report I want a running balance of
what is left of the order for their department to produce. I want the same
report for the Molding department. So far I have 8 tables and I have followed
Codd's rule as I understand it (each table has a pk and the foreign key is
only in one table as to not duplicate data). What I want is my tables
reviewed and some suggestions to make them better as I tend to over think
some things and totally miss others.
The Customers table and Shippers table hold just what they say, information
only related to those two subjects. Those two tables relate to the Orders
table via foreign keys in the Orders table. The Orders table is basic and
relates to the Order Details table. The Order Details table relates to the
products table. Up to here I feel pretty confident because I used the tables
directly from the Northwind sample database. Now where I get confused is
adding the ShoeCatagories table (Three distinct shoe styles), Componets table
(different sizes of Uppers for the three distinct shoe styles) and
FinishedShoe table (different sizes of shoes for the three distinct styles).
One of the things I am confused about is do I need to have two fields in
each of the two above tables (Componets and FinishedShoe) to show incomming
and outgoing shoe production for each table or can I show one numeric field
in each table, subtracting or adding one table totals from the other to
obtain production and inventory levels in each part of production the table
represents? A couple of examples are as follows:
Componets needed to be made (Uppers) would be represented by the "Quantiy"
in table "Order Detail", minus how many Uppers were made in table
"Componets", field "ComponetsInStock" equal the amount needed to be made for
that particular order?
If so then I would have to do a similar calculation for each report i.e. the
"Quantiy" in table "Order Detail", minus "FinishedShoe" table
"FinishedShoeUnits" which represent completed shoes equal the amount of units
needed to be molded/finished to complete the customer's order in table "Order
Details".
So this post has two requests/questions:
1) What I want is my tables reviewed and some suggestions to make them
better as I tend to over think some things and totally miss others.
2) Do I need to have two fields in each of the two above tables (Componets
and FinishedShoe) to show incomming and outgoing shoe production for each
table or can I show one numeric field in each table, subtracting or adding
one table totals from the other to obtain production and inventory levels in
each part of production the table represents?