Why do I need a details table?

[mjdemaris]

I've noticed that Access templates commonly use an Orders table and an OrderDetails table.

As I began developing this inventory database I have implemented this as Transactions and TransactionDetails. However, I have some problems tying various queries together and creating a certain feel and look for a form for a particular user - the main one!

So, I am wondering why use a separate Details table, and secondarily, do I need one?

Transactions:
TransID
TransDate
UserInitials

TransactionDetails:
TransDetailID
PartLocID_FK
TransTypeID_FK
TransID_FK
QuantityTaken
ReportedQtyLeft

Thanks,
Mike

 

[MarkK]

One order has many things. How do you propose to represent those many things without another table?

 

[jdraw]

I'm not sure of your background or development methods, but I recommend building a model of the proposed database based on business rules. Take some sample data and some test scenarios and "work the model"; adjust as needed until it meets your business requirements.

I don't think you will get to the tables you have if you consider

someone (a customer) makes 1 or more Orders, an Order is for 1 or many Items

This free data model is generic and may give you a broader picture of the "things" involved and how they are typically related.

As MarKK points out 1 Order can contain 1 or many Items -- hence the OrderDetails table or the Customer_Order_Product junction table in the free model.

see my stump the model info.

Good luck with your project.

 

[mjdemaris]

Well, I am pretty much a novice. I took a few classes several years ago as part of my degree program, but was never able to really put the education to use professionally - degree, no real experience, plus a moderate sized family equals no internships and too low of starting pay.

Now, I am doing this on my over time for a company, one or two days a week. I have been doing all this work "just to get it done" as there is pressure for a better system than the Excel programs we are currently using.

I admit, that I am feeling weary of this thing. I have a good idea of how each person uses the system, basically there are three human entities:
general user
stock taker
manager.

I have a good understanding of how the system works (work flow). Not real comfortable with terms like "business rules and requirements." I have a list of actions that each user can perform on the system.

Every week it seems I run into something that makes me question my table structure, because I can't do something I would like to do or I see a template database and wonder how they did that.

Here is a picture of the table relationships.

Tired and frustrated and grateful for all the experience and advice on this site,

Mike

 

[mjdemaris]

MarkK,
I don't know. I'm trying to figure it all out...

 

[gemma-the-husky]

if you don't have a details table, then you will find that for each row of the "order" you will be repeating certain items.

eg The customer, the order date, the order number.

Splitting the table into two to get rid of these repeating groups is the first step in normalisation

The idea is to store each item of data once and once only - no duplication.

 

[NauticalGent]

As much as I dislike what I am about to say, the NorthWinds sample DB does what I THINK you are trying to do and provides some excellent bells and whistles.

I have been known to plagiarize some of that application a few times...

 

[AccessBlaster]

...I have been known to plagiarize some of that application a few times...

Really there is no point reinventing the wheel. Northwind sample database should be the standard or starting point. I still reference the 2000 version, even though I am using 2016.

 

[The_Doc_Man]

MJ, the issue of having a separate order detail table and its parent order table is based on a simple rule, one of my "old programmer's rules." If you will ever want to know which orders contained left-handed veeblefetzers, there must be a place to store their sales records. So that is why you have a detail line-item table. BUT.... why do you need the PARENT ORDER table? Because of the concept of normalization, which is an optimization method used for the purpose of reducing the amount of data you have to manipulate when generating reports or creating summaries.

And why do you need a separate customer table, since an order goes with a customer? Because you will have a detail line item table but don't normalize, then you need to store the invoice order number and the customer name for every record in your details table, which rapidly gets very clumsy. Whereas if you have a customer table that has individual orders as its child table, and the order table has a details table that is ITS child table, you get away with not having to store so much information for every individual detail line.

If, for example, your invoice info included an 8-digit invoice number, a date, and a sales person name, and then your customer name had a name, delivery address, phone number, and billing address ... add up the space required for keeping that with each detail line. Or you could just try to do this in EXCEL - which is what you use UNTIL you learn why you want parent/child tables.

When you use the "relational" feature of Access, each link to a customer table typically takes no more than 4 bytes whereas names alone typically take 30-60 bytes - then you have addresses etc that probably quadruple that space. The invoice table link takes up 4 bytes whereas invoice numbers, dates, etc. take 8 bytes each and the sales associate's name is another 30 bytes or so...

Once you learn database normalization, you won't have to ask why you need a particular table - you will KNOW that you need it.

 

[stopher]

MJ, the issue of having a separate order detail table and its parent order table is based on a simple rule, one of my "old programmer's rules." If you will ever want to know which orders contained left-handed veeblefetzers, there must be a place to store their sales records. So that is why you have a detail line-item table. BUT.... why do you need the PARENT ORDER table? Because of the concept of normalization, which is an optimization method used for the purpose of reducing the amount of data you have to manipulate when generating reports or creating summaries.

If you look at a typical car sales database you will find they only have one table because there is only one car sale for each order. There are plenty such examples that do not require a header and detail table.

The OP talks about transactions which typically makes me think of financial transactions - typically one table because there is no transaction detail (unlike say a supermarket transaction).

The OPs diagram suggests there are many parts per order but is this because the OP thinks it is supposed to be done this way when in fact every order only ever has one part. I suspect the former is the case but my point is is it not the case that models always follow order header/detail model.

Chris

 

[gemma-the-husky]

If you look at a typical car sales database you will find they only have one table because there is only one car sale for each order. There are plenty such examples that do not require a header and detail table.

The OP talks about transactions which typically makes me think of financial transactions - typically one table because there is no transaction detail (unlike say a supermarket transaction).

The OPs diagram suggests there are many parts per order but is this because the OP thinks it is supposed to be done this way when in fact every order only ever has one part. I suspect the former is the case but my point is is it not the case that models always follow order header/detail model.

Chris

to go on from this, it becomes a matter of careful data analysis.
if 99% of your orders have only 1 part, but 1% of orders have more than one, then your system must deal with the general case of more than 1. It will still work with 1 part. But a system designed to work with one part, will not be useable when you have more than one part.

Also, you need to consider changes in the future. It might be one part now, but it might not be in the future.

 

[jdraw]

This is the line that suggests your current structure/approach may not meet your needs.

Every week it seems I run into something that makes me question my table structure, because I can't do something I would like to do or I see a template database and wonder how they did that.

Please give us a sample of one of those somethings.

As Chris and Dave have said, if every Order in your set up/environment is always for 1 and only 1 Item/Part, then No details table could be appropriate. But, if an Order can be for 1 or more parts, then the general advice/solution is to use an OrderDetail table.

As Dave said, it all comes down to data analysis, and you know your environment better than any reader does.

Good luck.

 

[The_Doc_Man]

Stopher, the OP's 1st post spoke to me of a sales inventory problem. If you saw it differently, of course you would answer differently. But the strength of this site is that we each see different cues and give different viewpoints to a single problem. Which in the long run isn't a bad way to learn. Sampling different viewpoints is rarely a bad thing.

 

[mjdemaris]

Thanks, Dave. I understand.
As far as Northwind, I’ve been playing with it and I concluded that it would be more difficult for me to decipher how it works and modify it to fit our needs than create my own from scratch.
The “somethings” come as I learn: form macros, tempvars, vba, recordsets, class modules…and from additional features the users want…
The Northwind database uses some class modules for error handling and recordset wrapper. I wondered if it would be better for me to implement something like that – but still working on deciphering some of that.
Now, I am attempting to create a spreadsheet look and feel for two of our users. However, this is proving to be quite difficult. Though someone shared a post by Pat somebody on another thread, I don’t think I can do it. Mainly, there are too many queries/tables involved. (Side issue) http://www.access-programmers.co.uk/forums/showthread.php?t=285103

So, let’s not get too carried away on this ordering bit – that will come in my next database. This is supposed to be an inventory program, and I hope you all can see the general idea in the structure.
Parts – Locations – Transactions (to capture each part related event: removal, restock, stock take)…
Doc, what is a veeblefetzer?

 

[mjdemaris]

So, the Northwind database has a combo box field in the Products table, that uses a SQL statement as the row source. Is this beneficial? Or should I use a junction table to join my parts table and suppliers table? Keeping in mind that we can have many suppliers for one part, which means storing multiple part numbers for one part...

Thanks.