Product Order/Pricing Database

[atg]

Hello,



I am new to AccessForums and have found the site to be incredibly helpful. I am a bit of a beginner, but look forward to contributing as I get more familiar with the program.

I am building a database that shows historical product orders and pricing. Specifically, the database relates to clothing manufacturing. I only included the primary tables as I am most concerned about the relationships I have set up. I tried to be concise while still including all necessary info. I apologize if I am a bit vague, but would appreciate any advice you may have.

Some details:

• Each order is given a unique GEDP and LotNo.
• A ClientStyleNo & FactoryStyleNo can be assigned to more than one GEDP, but a GEDP has only one of each.
• A FactoryStyleNo can have more than one CostingID. I will create a function later that calls the appropriate CostingID based on the season.

I felt that FactoryStyleNo was the most appropriate foreign key for tblCosting. Likewise, ClientStyleNo is most the most appropriate foreign key for tblStyle.

tblProduction shows an orders progression through the factory. tblDelivery gives specific delivery information. I believe each should have a one-to-one relationship since each an order (GEDP) is unique and only occurs once. To avoid overloading tblOrder with information, I split it off into separate tables.

Does this pass the eye test? I want to avoid any critical mistakes with the relationships before I begin building it out. Thanks!

[orange]

If GEDP is unqiue, why do you have joins on OrderId and LotNo.
The Del1PO,Del2PO... and the similar Date and Quantity fields indicate an unNormalized structure(s).
I'm not sure on how tblCosting and tblOrder are related

It is difficult ot comment on the tables and relationships.
Do you have a list of Business Facts/Rules? That is one technique to help validate your model to your business.

See http://www.databaseanswers.org/approach2db_design.htm

See the How are they related at http://www.databaseanswers.org/data_...arts/facts.htm for some basic business rules.

There is a more complex data model for Customers and Orders here
http://www.databaseanswers.org/data_...ries/index.htm It may not relate to your situation, but may help with some concepts.

Test your evolving model with sample data (good and bad data); adjust as necessary; and repeat until you can reconcile any differences.

Good luck with your project.

[atg]

Thanks for the help, orange! Your advice definitely lead me in the right direction.

[orange]

Good stuff, glad to hear you project is proceeding.