Sample DB like Northwind that is intuitive (1 Viewer)

Hi all -

Without going into a long drawn out story - the powers that be at work love what I am doing with my QC db.....so much so that I've been tasked to develop another one that initially was to record inspections of hardware: incoming, purchased on a PO and later shipped out with a job.

This opens a broad horizon in front of me. Our purchasing department struggles mightily with our ERP primarily because the ERP was outsourced and we have no control over form or query design - and are not nearly big enough to swallow the cost of DB ongoing development - especially when our industry is a niche market at best.

I took a look at the Northwind sample db and - from the outside - it looks like it would fit the bill easily (although I would nix the PO submittal and approval part of it.

So I started trying to figure out the workflow of it - and was instantly stymied. I can't figure out how to approve a PO. I can't create a new PO. etc. etc.

What I was hoping to explore - and is my only question - is how does Northwind handle receiving partial purchase orders - meaning a purchase order is submitted for purchase of one item, quantity = 10K but only 2000 can be shipped at a time.

I wanted to see what that might look like on the back end of things - particularly table design and any calculated fields.

But Northwind doesn't seem to actually work.

So - -- - does anyone know of a sample database that is similar to Northwind that actually works?

I'm kinda bummed because if Northwind actually worked I can immediately see how I could change table and field names and truly be 70% of the way down the road.

Thanks,

Tim

Hi Tim. Congratulations on your success with your db at work. I don't have a sample for you either but I would think partial deliveries would be modeled using a child table; therefore, using a subform, to record all the fulfillment of a specific order. For instance, if the order was for a qty of 100, then either one delivery of 100 or multiple deliveries up to a total of 100 will be in the subtable.

Tim,
There are several generic data models at Barry Williams' site. You should understand your company processes and procedures before jumping to any database or data model.
The data models at his site are meant as a starting point - you can add/modify/remove parts that don't work for your environment. As I have mentioned previously, getting your tables and relationships designed, tested and vetted is critical to a successful database.
Buying or implementing a solution that doesn't quite match your business can be more costly than expected. Change in processes, change in forms/reports., trying to adapt to new names or tables etc... can cause confusion and errors.

If you are in a niche market, you may have custom/unique processes and procedures. Better to model your environment and determine your requirements, then decide on feasible alternatives for development/implementation before buying or accepting a new database.

Glad your work is being accepted by management and their encouragement to extend your database activities.
Good luck with your project.

I just saw NG's post and thought I should add this link to several articles in various formats re Database Planning and Design etc.. It includes some of the RogersAccessLibrary materials. If I sent this link to you previously, then here it is again-- these represent a collection of things that I have found useful, clear and practical.

Tim,

Give this site a look over...

http://www.rogersaccesslibrary.com/forum/database-design_topic238.html

Edit: seems I am not fast enough to compete with DBG or Jack!

I always advise my prospective customers to try off-the-shelf Solutions before they venture into creating a bespoke MS Access database. And I think this is what you have here, I am sure there will be off-the-shelf Solutions to do what you want. However as you have discovered with the Northwind database, a ready-made solution often lacks the particular functionality that you require. That's the time to start creating a bespoke database.

I would also advise against starting with an existing database. One of my biggest failures was the result of trying to modify an existing database a customer had asked me to improve. You are far better off creating your own database, and copying, adding, functionality you can see (and understand) from other databases.

If you are starting out with one of the Northwind sample databases, in particular the original earliest database provided by Microsoft, then there are some problems you should be aware of. I demonstrate the problems and also demonstrate how to fix them on my blog here:- http://www.niftyaccess.com/northwind-problem-and-solution/

theDBguy said:

Hi Tim. Congratulations on your success with your db at work. I don't have a sample for you either but I would think partial deliveries would be modeled using a child table; therefore, using a subform, to record all the fulfillment of a specific order. For instance, if the order was for a qty of 100, then either one delivery of 100 or multiple deliveries up to a total of 100 will be in the subtable.

Click to expand...

Thanks - for everything.
That's the advice I needed. I think I can model something like Northwind and add a child table as you suggest.

It'll take some tweaks and cuts and this and that - - but the general model is what I am after. It very much represents our workflow - - - which I know really well at this juncture. Customers will become JobNumbers....among other things.

You know I'll post as I progress.....

And still working on the other one as well. I think I am finally at the point where I am debugging and adding error handling throughout on the QCDB.

jdraw said:

Tim,
There are several generic data models at Barry Williams' site. You should understand your company processes and procedures before jumping to any database or data model.

Click to expand...

I understand them pretty well - - and thanks to your advice I know my first step is to sit down with the retiring Purchasing Manager and put his process to paper and diagrams.

jdraw said:

As I have mentioned previously, getting your tables and relationships designed, tested and vetted is critical to a successful database.

Click to expand...

Yep - see above.

jdraw said:

Buying or implementing a solution that doesn't quite match your business can be more costly than expected. Change in processes, change in forms/reports., trying to adapt to new names or tables etc... can cause confusion and errors.

Click to expand...

That's the truth. That's the problem with the ERP we use. The company kinda got "suckered" in a way.....by themselves actually - because the company didn't actually understand their own business model and how robust a data management system would need to be to accommodate growth and change. It is well-known and well-accepted throughout the company now.

jdraw said:

Glad your work is being accepted by management and their encouragement to extend your database activities.
Good luck with your project.

Click to expand...

Thanks! I have had a lot of help from all of you!

jdraw said:

I just saw NG's post and thought I should add this link to several articles in various formats re Database Planning and Design etc.. It includes some of the RogersAccessLibrary materials. If I sent this link to you previously, then here it is again-- these represent a collection of things that I have found useful, clear and practical.

Click to expand...

You did .... and I benefit from a reminder! Thank you!

Tim

NauticalGent said:

Tim,

Give this site a look over...

http://www.rogersaccesslibrary.com/forum/database-design_topic238.html

Edit: seems I am not fast enough to compete with DBG or Jack!

Click to expand...

Much Appreciated! Thank you

theDBguy said:

I would think partial deliveries would be modeled using a child table; therefore, using a subform, to record all the fulfillment of a specific order. For instance, if the order was for a qty of 100, then either one delivery of 100 or multiple deliveries up to a total of 100 will be in the subtable.

Click to expand...

I was hoping I could get Northwind to work as I am really wanting to review how table Purchase Order Details works for this very reason. I am wondering if it is acting like the child table that you mention.

I'm going to read some On Load and After and Before Update code now. instead of watching Game of Thrones.

Zydeceltico said:

I was hoping I could get Northwind to work as I am really wanting to review how table Purchase Order Details works for this very reason. I am wondering if it is acting like the child table that you mention.

I'm going to read some On Load and After and Before Update code now. instead of watching Game of Thrones.

Click to expand...

Unfortunately, I am not very familiar with Northwind, and I don't have one right now to evaluate, so I can't tell you how the Purchase Order Details work. However, I have created other purchasing databases before. I don't treat it any different than my own projects database. I think they're very similar. For instance, I might have a table of clients and each client would commission me to do a certain project, and each project would consist of several tasks. So, I have the clients table as the parent table, the projects table as a child table of clients, and the project details as a child table of projects. In the same vein, I imagine you would have maybe a departments table as a parent table to purchases, and purchase details table as a child of the purchases table. As far as partial deliveries, it could be a child table to the items in the purchase details table.

theDBguy said:

Unfortunately, I am not very familiar with Northwind, and I don't have one right now to evaluate, so I can't tell you how the Purchase Order Details work. However, I have created other purchasing databases before. I don't treat it any different than my own projects database. I think they're very similar. For instance, I might have a table of clients and each client would commission me to do a certain project, and each project would consist of several tasks. So, I have the clients table as the parent table, the projects table as a child table of clients, and the project details as a child table of projects. In the same vein, I imagine you would have maybe a departments table as a parent table to purchases, and purchase details table as a child of the purchases table. As far as partial deliveries, it could be a child table to the items in the purchase details table.

Click to expand...

The only difference - and I think it is fortunate - is that we don't have multiple departments - only one. I am going to work on a child table for Purchase Order Details.

Tim,

One little thing to consider before going further, head down to where you are doing receiving and ask them some questions. Find out if you do get partials, find out if you get a shipment that covers multiple POs, and find out if there is anything that is really bugging them about the current system that you’ll need to resolve.

Finding out from the end users what exceptions there are in reality can often give you a lot more insight than asking management how things are supposed to work.
My recommendation for tables is rather straight forward.

TblReceiving
-Who is receiving an order
-Which PO(s) the order is for
-When the order arrived
-Notes regarding the order

TblReceivingItem
-Line item info for each piece on the packing slip(s)
-FK to PO Detail item that this is part of
-Quantity received


The design philosophy is to have one or more Receiving Items as linking records between your Purchase Order and your Receiving table. They do effectively become child records to each PO line item though. To see if all items are received (so you can mark a PO’s Detail as “Delivered”) you’d total up the quantity received and compare it to the PO Detail’s quantity ordered. Once everything is delivered you can mark that item as “Delivered” (I’d use the date the last item arrived) and then roll this up for the order itself to when the last item in a PO is delivered to mark it as delivered.

This also gets into a bit of user interface design; your “Receiving” module should allow your receivers to enter new deliveries and link them to existing POs. You can have more than one delivery for a given PO and (possibly) more than one PO for a delivery.
This is and should be a rather small piece that sits on its own, only used by whoever accepts shipments. It may also expand to include a calendar to schedule when deliveries are received and contact management for shippers and vendors.

Seriously - this sort of thing can grow like Topsy.

In any substantial database exercise, you need to start with a specification, and a design phase. Talk to the people involved, Find out what they want. Include for every eventuality. If 99% of the time, orders are delivered intact, but two or three a month are delivered in phases, then you have to design for phased delivery as standard. You need to detail all the data elements you need to model, and design appropriate table layouts.

Only after you have done this do you decide what forms and queries you may need, and start to develop the functionality. For a tiny system you understand at the outset, you might "know" what you need, and just crash straight in, design the tables, and start development. You will probably have a number of standard utilities you include in all your databases - a table linker, a report module to take a query, and either output it to screen, or save it to an excel spreadsheet, or a csv. A standard import routine to do similar stuff.

If your people are struggling with a professional ERP system that has most likely had thousands of man hours spent on it already, replacing or supplementing this with a bespoke system is a big project. The chief benefit of bespoking a system is that it is more tightly designed for your company, and doesn't include stuff you never use. Also you save on support and development costs, as it's yours already. On the other hand it's likely to be less impressive than the commercial package, as it probably won't include a lot of bells and whistles, such as a F1 help manual.

Systems get intuitive when they are well analysed and designed.

A small departmental system is one thing.When it becomes adopted by the entire company, and becomes mission critical, it needs a different level of robustness.

The bigger the sytem the harder this becomes - and the more time consuming. Depending how complex your buying process is, you could easily spend a month solid (or more) getting to a deliverable stage.

Good luck, but don't underestimate what you are taking on.

Well said, Dave. Great advice.

When you take this particular garden path you are about to explore, ALWAYS keep two simple ideas in mind.

1. Every business-tracking database depends on having FULL KNOWLEDGE of the formal business flow and procedures of the business, because your database will be a MODEL of your business - a data-flow simulation as it were - of what you do in that business at every level. Can't build a model if you don't understand reality.

2. Whenever you work on your model and you find that the model differs from the actual flow of business data, THE MODEL IS ALWAYS WRONG and the reality of your business is ALWAYS RIGHT. (Your "customer" is your business and the customer is always right.)

Adding to that, one more comment (as an observation):

not nearly big enough to swallow the cost of DB ongoing development

Click to expand...

You are about to embark on an ongoing-development DB project because it will NEVER be finished. The moment you THINK you are finished, someone else will say, "Did you hear about the new regulations?" or "Oh, when we talked last month I forget to mention how we handled left-handed veeblefetzers." Or something like that.

Further, congratulations are in order for having stumbled across a way to make your boss notice you. Just try to not stumble now that you have been noticed. But this could eventually be a key to merit raises and to making yourself a more valuable employee. BUT you have also discovered a reality of the world of work. That which you have done successfully follows you around like stink on a skunk. You now have a reputation of being someone who does useful things. Don't be surprised when you get barraged with things to do.

gemma-the-husky said:

Seriously - this sort of thing can grow like Topsy.

A small departmental system is one thing.When it becomes adopted by the entire company, and becomes mission critical, it needs a different level of robustness.

The bigger the sytem the harder this becomes - and the more time consuming. Depending how complex your buying process is, you could easily spend a month solid (or more) getting to a deliverable stage.

Good luck, but don't underestimate what you are taking on.

Click to expand...

Very, very good advice.........that I have already heeded - - - this morning.

Our ERP is in SQL Server. It is off-the-shelf with some custom forms and tables specific to our business model. The biggest challenge with using it is that ours is a larger-than-small family-run company making a product that we've basically made the same for the past 50 years. In the past 15 years, the company began "enhancing" the product to carve out a nice market niche.....and the future is coming to greet us.

So we have this big ERP of which many major features are not utilized.

Reading your post this morning after spending the late evening considering the scope of what I have been considering undertaking led me to have a conversation with our general manager about the inventory process and what I was beginning to clearly see as "too-big-pour-moi." I explained to him that we (those above me who can make bigger decisions than I can) should really look at opening up and utilizing some of the more robust features of our existing ERP before I try to undertake this. I took advantage of their enthusiasm for my current work to emphasize the need to "get on it" with the ERP because the new task they want me to do is less about inventory and more about inspection audits but the inventory part (specifically the issue of partial POs) has to be in place and taken well into account before I can make my part work.

He seemed pretty enthused to have a new reason to initiate more functionality out of our not-inexpensive ERP. Basically, he is still happy with me because he now has a good reason to insist that the rest of the company learns and uses more of the ERP which he originally made the argument for purchasing - -so he gets some validation and my scope becomes far more manageable.

So - - - - - thank you for the words of advice.

It mattered - and it is working out well.

Thanks,

Tim

Tim,

If need be, I'm SURE some of us can get dress up nice and ask for an excessive amount to come visit so we can tell your company it would be cheaper for them to actually use the system they paid for rather than pay us to help you remake the system they've already paid for....

To jump to the punch line: Thanks! and.........I've already proactively limited my scope to something I know is manageable - - - and somehow retained the good graces of upper management.

The_Doc_Man said:

When you take this particular garden path you are about to explore, ALWAYS keep two simple ideas in mind.

1. Every business-tracking database depends on having FULL KNOWLEDGE of the formal business flow and procedures of the business, because your database will be a MODEL of your business - a data-flow simulation as it were - of what you do in that business at every level. Can't build a model if you don't understand reality.

Click to expand...

Our company is relatively small (200 employees or so) and I am blessed with a LOT of latitude in having conversations with basically anyone. The folks in accounting and estimating look at me like I'm crazy when I start asking business process questions but come around quickly when I tell them why I am asking.

The_Doc_Man said:

2. Whenever you work on your model and you find that the model differs from the actual flow of business data, THE MODEL IS ALWAYS WRONG and the reality of your business is ALWAYS RIGHT. (Your "customer" is your business and the customer is always right.)

Click to expand...

Yep - via working through the process of building the QC DB I have learned more about the inner workings of our real process than I would have ever learned just doing my assigned job for the next 10 years....and "The Model" would have certainly been wrong. I am already modifying workflow on a couple of forms to better reflect what we actually DO rather than what thought we were doing.

The_Doc_Man said:

Further, congratulations are in order for having stumbled across a way to make your boss notice you. Just try to not stumble now that you have been noticed. But this could eventually be a key to merit raises and to making yourself a more valuable employee. BUT you have also discovered a reality of the world of work. That which you have done successfully follows you around like stink on a skunk. You now have a reputation of being someone who does useful things. Don't be surprised when you get barraged with things to do.

Click to expand...

Amein - it has begun - lol

Mark_ said:

Tim,

If need be, I'm SURE some of us can get dress up nice and ask for an excessive amount to come visit so we can tell your company it would be cheaper for them to actually use the system they paid for rather than pay us to help you remake the system they've already paid for....

Click to expand...

LOL - I'm sure you're right. Actually - there never was a QC system in the ERP. It is primarily an estimating, inventory, allocation, shipping/receiving tool, AR/AP......that is hugely underutilized. In the same conversation this morning I convinced the general manager that he should turn on the feature that automatically sends an email to invested parties when inventory quantity hits a target balance. But no - - there is no QC aspect to it at all. Until recently, the ERP has been used more like an enormous Quickbooks rather than anything else.

The existing QC DB that I may have mentioned was built by a very well meaning gentleman about 15 years or so ago - in Access - but is really no more than spreadsheets in something other than Excel......which is why - I think - they stopped using even that about 10 years ago and WENT BACK to paper for QC inspections.


So.................yeah................there's that. LOL

BTW - I'll have been here for two years this coming September fwiw ( in other words......none of this is my fault - lol)