Accounts and Orders DB

[Leo_Coroneos]

Hi everyone, here's a screen snip of my Relationships diagram for my Accounts & Orders Database for Wakes Music: see attachment.

I'm pretty confident that this is what I need and how I need it. Any and all suggestions, however, are most welcome!

Cheers,
LC.

 

[Ranman256]

It seems fine. Tho you dont really need the tblProduct hooked to the ProductOrder.
tProduct is more of a lookup to add data to the ProdOrder table.
It doesnt really need a relationship, but it wont hurt it either.

Relations are for parent/child. 1 order has Many ProductOrders (yes)
1 client has many accounts.

Product relation to ProdOrders, (optional since its just a lookup)

 

[Minty]

I'm not sure you should have CR DR and Balance fields in your accounts - unless you really are trying to replicate a full double entry accounts system. (Just don't - there is a very good reason people buy such things.)

Normally you store transactions in one column, either positive or negative depending on the transaction type, then simply calculate the balances on the fly using a sum on that column.

Trying to keep a running balance correct is fraught with complicated issues.

 

[Mark_]

Several items pop up to me...
1) In tblProductOrder there is no Amount for a given product. How do you determine what a given product costs? Same with it missing from the TblProduct.

2) In TblAccount you have Debit, Credit, and DR/CR. What are each used for? Also I'd remove the special character from DR/CR and simply call it DrCr.

3) In TblClient I'd remove Phone, Mobile, and Email. I'd have a child table "TblContact" with a Contact_ID, ContactType (string, looked up if you wish), and ContactValue (string, formatted based on ContactType). This avoids the whole issue when your client has both business and personal Emails as well as a business phone, personal phone, and "Summer home line", but no "Mobile". Phone, Mobile, and Email are all values for ContactType, not unique fields themselves.

For TblAccount is this really going to be a chart of accounts? If so you'd be better off having (or replicating) journal entries rather than having a linking table.

 

[Leo_Coroneos]

Wow, ok guys, let me take all this info on board gradually. Thanks for your input Mark, Minty and Ranman

Let's see. The Account table is an exact replica of my client's account slip (see attachment: WakesAccountSlip.jpeg), and he is happy with it.

Your suggestions, however, lead me to believe that it may be a bit clunky in that (Account) area. Thus, I've stripped the database down to just the Account section (see attachment: WakesAccountDB_Relationships.JPG) so we can look at it in more detail.

I'll revise my Data Dictionary for this database and put it online. Some time before or after that, I will read through all your posts thoroughly so I can implement the best suggestions. It's just a bit TMI at the moment! I must be a bit rusty, having had several months' break from IT school to potter around the house and just do nothing and let my DB-design skills degenerate. Sorry 'bout that...

But yeah, thanks again!

 

[Leo_Coroneos]

@Mark:

1. I'll be focusing more on the Order form after thoroughly working out and getting right the Account form. My client says that it doesn't matter what a product costs, he just writes the product ID and quantity when he orders.

2. Debit/Credit are used for subtracting and adding currency to a client's account. Does that make sense? Hopefully it does...

3. I'm a bit confused by this. It is said that beginners often make the mistake of throwing everything into a few big tables, and maybe I've made this mistake, despite being at intermediate level (I think, hahah). My client is quite blase about what goes in the contact table, he just wants a name and phone number at the very least. Thus, the Email, Phone and Mobile fields are not required, and could probably just stay in the Client table.

(See attachment)

Thanks again guys.

 

[Minty]

Leo - Point 2. As I stated trying to replicate a proper double entry accounting system is really hard work, and certainly not worth the effort.

If all you need is to keep track of someones balance, then a simpler transaction record is all that is required. An input is a positive value an output is a negative value, in one column.

By all means I would suggest you include a transaction type, date and even a free text description, but keep the tracking of the balance simple. Don't try and store it.
You can have a transaction type that is a Balance check or period end. you then simply sum from the last one of those.

 

[Gasman]

My client is quite blase about what goes in the contact table, he just wants a name and phone number at the very least. Thus, the Email, Phone and Mobile fields are not required, and could probably just stay in the Client table.

Isn't that where your expertise is meant to come in?

My bosses know nothing about programming, even less than me but even I can see where something I implement will be useful in the future.

Take emails for instance, you send something to one person at the moment, so only need one field/one record. Then they ask 'can you copy/send to Joe Blogs as well please.
Do you add Email2 field to the client record, then they ask for another, or do you have a table for Contact/Emails and retrieve all records for that company/entity?

 

[Leo_Coroneos]

Isn't that where your expertise is meant to come in?

Yep, quite so!

My bosses know nothing about programming, even less than me but even I can see where something I implement will be useful in the future.

Take emails for instance, you send something to one person at the moment, so only need one field/one record. Then they ask 'can you copy/send to Joe Blogs as well please.
Do you add Email2 field to the client record, then they ask for another, or do you have a table for Contact/Emails and retrieve all records for that company/entity?

I've designed two databases before, both with only one Customer or Client table, and that seemed at first like the best idea for my Wakes Music DB.

I am paying good attention to you guys at the moment because I don't want to screw this up for my very first real client. However, it kind of irritates me to have to make adjustments to what I considered a perfect schema design. (When I started this thread, I was really just anticipating a few pats on the back and messages of congratulations on a job well done. )

Obviously, what I have omitted to bear in mind is that this is a prototype that needs alpha, beta and final testing. That is what I was taught at TAFE college over the period of six months using Access non-stop. There's that design cycle of customer feedback, implementation of further requirements, revisions to the program, etc. etc. So let's get back to the problem at hand.

Shall I make a new table called something like tblContact or tblContactdetails and a) put the Email1, Email2, Company, Phone and Mobile in there, along with b) a foreign key CID referencing tblClient as a one-to-many relationship? Am I right in surmising that this is what you guys recommend?

Cheers--Leo.

 

[Leo_Coroneos]

Leo - Point 2. As I stated trying to replicate a proper double entry accounting system is really hard work, and certainly not worth the effort.

If all you need is to keep track of someones balance, then a simpler transaction record is all that is required. An input is a positive value an output is a negative value, in one column.

By all means I would suggest you include a transaction type, date and even a free text description, but keep the tracking of the balance simple. Don't try and store it.
You can have a transaction type that is a Balance check or period end. you then simply sum from the last one of those.

Yes, that's all correct, spot on. Cheers, I will implement this.

 

[isladogs]

I am paying good attention to you guys at the moment because I don't want to screw this up for my very first real client. However, it kind of irritates me to have to make adjustments to what I considered a perfect schema design. (When I started this thread, I was really just anticipating a few pats on the back and messages of congratulations on a job well done. )

Ha ha ....
Actually you have made a good start but your idea of deleting data was definitely a bad idea ...

 

[Leo_Coroneos]

Here's the Order slip for the other section of the database (see attached).

 

[Leo_Coroneos]

Ha ha ....
Actually you have made a good start but your idea of deleting data was definitely a bad idea ...

I know :banghead:

Something tells me that the report I ought to generate from the Order form should have a filter applied to it, in order to display the correct client details... any other ideas or elaborations on this one?

 

[Minty]

The simple route is to include the forms OrderID in the criteria of the reports underlying query.

This will obviously only work if the OrderID is unique... (see where we are heading with this)

 

[Leo_Coroneos]

Ok, but it doesn't have to be an AutoNumber, does it? It just has to have a unique index on it.

What about composite indexes, like for City/State/Postcode? My Access Bible tells me that they are worth using to enhance performance. They're something I didn't learn at TAFE.

 

[Mark_]

Shall I make a new table called something like tblContact or tblContactdetails and a) put the Email1, Email2, Company, Phone and Mobile in there, along with b) a foreign key CID referencing tblClient as a one-to-many relationship? Am I right in surmising that this is what you guys recommend?

Cheers--Leo.

Nothing near like that...
Table would have the following fields
ContactID............AutoNumber........Primary Key
Cust_ID..............Number...............Foreign Key to parent
ContactType........Text...................What type of contact is this?
ContactDetail.......Text...................What the contact really is


In your data it will look somewhat like this.

1...1....Phone....6064845586
2...1....Email.....Null@Null.Dev
3...1....Mobile....44588663355
4...2....Phone....8855115335
5...2....Email.....Andra.Test@null.Dev

So you can have as many different types of contacts related to a given parent records as you wish. No need to add extra fields for them, just add one record for each. That way when they decide "Crap, we need to add their mother-in-law's EMAIL address" you already have an easy way to store this.

Could even go so exotic as to have type and sub-type as two fields, since they store different types of data.

Remember, a contact is a contact is a contact, regardless of what "Type" it is. YourEmail@YourISP.AU is just as valid as 995649840 for your home phone, 564980800 for your mobile, or MotherInLawIsME@HerISP.AU. Type (and possibly subtype if you want to get really excessive) would be used to format numeric entries.

 

[Mark_]

Pat,
As confusing is it may sound, I think his customer uses "Account No" the way we would think of "Customer No". The posted slip is also very odd as it uses Credit and Debit amounts along with a column for "Credit or Debit".

Personally I'd hate to have to look at his books. This would not be something I'd wish to delve into.

 

[Leo_Coroneos]

My client's books? Well, they must work ok on his end, since he's been in business for decades now and is showing no sign of relenting. Still, when I saw how he wrote out order by painstaking order, my heart was gripped with pity and I said to myself, "I must make this man's business operations more efficient, insofar as it is within my power to do so."

I'll give your suggestion a try, thanks, Mark (and everyone).