Some conceptual questions regarding db design similar to Northwind

[ShovelinFishHeads]

Some conceptual questions regarding db design similar to Northwind

I am currently working on designing a db that requires tables similar to Northwind for Suppliers, Products, Orders, and Customers; except that in my case the tables are: Suppliers, Work Description, Orders, and Contractors. Where I would like to depart from the Northwind model (or would need to depart from the Northwind model) is that I would like to have PK indexes for these four tables that all "autonumber"; And, my Contractors table has to have lots of fields with one to many relationships for Employees, equipment selection, and more which is very much unlike Northwind's Customers table.

I plan to use one query that joins the Suppliers and Work Description tables to drive the top half of an "Order" form while the bottom half is a subform that is driven by a query that joins the Orders table and the Contractors table. Link Master Fields would be WorkDescriptionID (PK of the Work Description table) and Link Child Fields would be WorkDescriptionID (FK in the Orders table). Doing this provides a way of having the necessary Supplier and Work Description info on the form along with the Order and Contractor info also on the same form. Making the Order form work this way enables the user to match Work Description requirements with Contractor skills, abilities, Licenses, equipment, and other contractor resources.

The issue that I am struggling with the most is whether or not an Orders table that is joined to the Contractors table could have a functioning Autonumber PK because the Contractors table "has to have" duplicate PK numbers in it due to multiple Employees, multiple Equipment resources, multiple Licenses etc. Doesn't that make the Autonumber process in the Orders table impossible? If it is impossible, how could I work around that?

One solution I thought about is using a "Prequery" between the contractors table and the query that joins the Contractors table with the Orders table. The "Prequery" only pulls a few basic pieces of ID info from Contractors table so it has no duplication of PK numbers. This strategy creates another problem, however, which is: how do I join all of the other contractor info to the order form and make it a part of the order?

Any and all help is appreciated. Thanks everyone

 

[plog]

You mostly lost me. But this being the internet and all, I'm not going to let that stop me from assuming I fully know the issue and offering advice. I focused on this:

The issue that I am struggling with the most is whether or not an Orders table that is joined to the Contractors table could have a functioning Autonumber PK because the Contractors table "has to have" duplicate PK numbers in it due to multiple Employees, multiple Equipment resources, multiple Licenses etc. Doesn't that make the Autonumber process in the Orders table impossible? If it is impossible, how could I work around that?

First, every table can accomodate an autonumber primary key. The sole purpose of it is to allow you to uniquely identify a row inside of the table. That's it. It's actually helpful to not even consider the autonumber primary key as part of the table--it has no relevance to any of the "real" fields in the table.

Second, I think the problem you are having is trying to envision a many-many relationship. For simplicity sake let's just consider Contractors and Resources. 1 Ccontractor can use many resources and 1 resource can be used by many contractors. That's a many-many relationship. The way you achieve that in a database is with an associative table (https://en.wikipedia.org/wiki/Associative_entity).

Let's assume the primary key of Contractors is ContractorID and ResourceID is that of Resources. Then the junction table between them would be like so:

ContractorResources
ContractorResourceID, autonumber, primary key
ContractorID, number, foreign key to Contractors
ResourceID, number, foreign key to Resources

ContractorResources would then be able to keep track of what contractors use what resources, and vice versa. Further, because that table itself has a primary key, if you need to reference a specific Contractor/Resource permutation, you can use ContractorResourceID to do so.

 

[jdraw]

Can you tell us in plain, simple English what the business is that this database is intended to support?
Simple terms, no jargon just as you would describe things to a 6 yr old or an 80 yr old granny.

Don't confuse describing what you are trying to do with how it could be done.

Get your tables and relationships designed according to your business requirements. Test your evolving model with test data and scenarios for what you should get from the database. Then develop your database --queries, forms, reports.

Also: Are you aware of the free data models at Barry Williams' site.

 

[ShovelinFishHeads]

Suppliers are shippers or brokers that handle shipper's shipping requirements.

Work Description describes specific shipments such as: weight, dimensions, special equipment requirements, special tool and hardware requirements, special permitting requirements, special endorsement requirements, pick up and drop locations along with pick up and drop timing requirements among many other things.

Contractors are carriers that transport shipments, across town or across the continent, via 18 wheel truck and trailer combinations.

Orders would be the matching of carriers to specific shipments such that all requirements are met and planned for correctly.

The object of the db is to manage everything that is involved in coordinating the efficient flow of shipments: all contact info, describing the shipment and all its requirements, order placing and order tracking, invoicing for the shipment, and collections. So, its a CRM for the freight business.

Hopefully, any jargon or technical terms I may have used is not making this more difficult to understand or too distracting.

 

[ShovelinFishHeads]

Maybe it would easier to understand my question if I put it like this:

In what ways would you suggest altering the Northwind db if we now required the following:

[1] The PK in the Customers table must now be formatted to Autonumber. Would accomplishing this necessitate altering any other table or relationships among the Northwind tables?

[2] The Customers table must now have several one-to-many relationships to other tables besides the Orders table such that there are multiple Employees, for example, along with Employee qualifications and that this employee information must become a part of each Order such that this information flows through to Orders.

Can anyone answer?

 

[jdraw]

With respect you are asking a HOW question in my view and you haven't got the detailed requirements for WHAT. The subject seems to to along the lines of Trucking/Shipping/Logistics as I read your post.

Do you need a communications function that interacts with trucks or a GPS aspect of where any truck/shipment is at anytime?
There is some Shipping Tracking System modelling info here.

Logistics model -high level

Logistics --more detail

I'm not trying to dissuade you from pursuing the Northwind database, I'm suggesting you get your requirements documented and confirmed before jumping to a solution.

Look at the features of some available commercial Logistics packages and see if any are needed by your organization. See if such features exist in Northwind or could be incorporated. Make a data model of your desired database based on our business processes and needs. This will lead to a blueprint. And then compare that with Northwind structure (or an adaptation of it).

Too many people jump to a solution before defining (understanding) their real requirements and get into significant design related problems after they have committed a lot of time with forms and code. Things that could have been made simpler or avoided with better design.

Good luck with your project.

 

[ShovelinFishHeads]

jdraw,

Thanks for all of the advice. GPS is not yet an issue I wish to confront in the near future. Might have to deal with it as a necessary "evil" down the road a ways.

As for the nuts and bolts of getting to where I want to be regarding Order taking and including all of the info I would like to see on each Order (item [2] above), I'm pretty sure I can get this done by putting FKs from an Employees table, Trucks table, Trailers table, etc into my Orders table. I'll just have to synchronize combo-boxes to the selection made for Carriers in the Orders table.