Accounts and Orders DB (1 Viewer)

Leo_Coroneos

Registered User.
Local time
Today, 17:37
Joined
Nov 27, 2017
Messages
107
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.
 

Attachments

  • WakesDB_ER-Snip2.zip
    26.4 KB · Views: 200

Ranman256

Well-known member
Local time
Today, 05:37
Joined
Apr 9, 2015
Messages
4,337
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

AWF VIP
Local time
Today, 09:37
Joined
Jul 26, 2013
Messages
10,353
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_

Longboard on the internet
Local time
Today, 02:37
Joined
Sep 12, 2017
Messages
2,111
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

Registered User.
Local time
Today, 17:37
Joined
Nov 27, 2017
Messages
107
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... :rolleyes:

But yeah, thanks again!
 

Attachments

  • WakesAccountSlip.jpeg
    WakesAccountSlip.jpeg
    63.7 KB · Views: 194
  • WakesAccountDB_Relationships.JPG
    WakesAccountDB_Relationships.JPG
    47.6 KB · Views: 189

Leo_Coroneos

Registered User.
Local time
Today, 17:37
Joined
Nov 27, 2017
Messages
107
@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.
 

Attachments

  • WakesAccountDB_EditClient_Snip.JPG
    WakesAccountDB_EditClient_Snip.JPG
    61.7 KB · Views: 186

Minty

AWF VIP
Local time
Today, 09:37
Joined
Jul 26, 2013
Messages
10,353
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

Enthusiastic Amateur
Local time
Today, 09:37
Joined
Sep 21, 2011
Messages
14,037
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 :D 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?
 
Last edited:

Leo_Coroneos

Registered User.
Local time
Today, 17:37
Joined
Nov 27, 2017
Messages
107
Isn't that where your expertise is meant to come in?

Yep, quite so! :eek:

My bosses know nothing about programming, even less than me :D 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. :p)

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

Registered User.
Local time
Today, 17:37
Joined
Nov 27, 2017
Messages
107
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

MVP / VIP
Local time
Today, 09:37
Joined
Jan 14, 2017
Messages
18,186
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 ....:D
Actually you have made a good start but your idea of deleting data was definitely a bad idea ...
 

Leo_Coroneos

Registered User.
Local time
Today, 17:37
Joined
Nov 27, 2017
Messages
107
Here's the Order slip for the other section of the database (see attached).
 

Attachments

  • WakesOrderSlip.jpeg
    WakesOrderSlip.jpeg
    71.7 KB · Views: 156

Leo_Coroneos

Registered User.
Local time
Today, 17:37
Joined
Nov 27, 2017
Messages
107
Ha ha ....:D
Actually you have made a good start but your idea of deleting data was definitely a bad idea ...

I know :eek: :banghead: :eek:

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

AWF VIP
Local time
Today, 09:37
Joined
Jul 26, 2013
Messages
10,353
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

Registered User.
Local time
Today, 17:37
Joined
Nov 27, 2017
Messages
107
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_

Longboard on the internet
Local time
Today, 02:37
Joined
Sep 12, 2017
Messages
2,111
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:37
Joined
Feb 19, 2002
Messages
42,970
Accounts and Orders are not typically implemented in the same application but if that is what your client wants, then you can do it but it is more complicated than what you have envisioned. Usually, it is a transaction table that feeds the GL because the transactions go beyond just an order. You have to also consider adjustments and returns at a minimum. But again, I don't know your scope so I don't know how accurate a representation of reality you need. As others have mentioned, it is a lot of work to create a proper accounting system and given the state of technology, I would buy one rather than building one. Order entry apps are much more custom and so those are more likely to be built in house. In that case, you would create transactions in the Order Entry app to send to the purchased accounting app to record your sales.

It doesnt really need a relationship, but it wont hurt it either.
Yes it does need a relationship. You wouldn't want to be able to delete a product if it existed on an order would you?

If you want to use the ORDER numbers from the form, you can use that as the primary key of your table. You don't need an autonumber. You only need uniqueness.

Unique Composite indexes are useful for junction tables and when you have a business rule to implement. So for example, in a student-class junction table, you would have an autonumber primary key because this table would have child tables (test and lab scores) but you need to ensure that any student is only enrolled ONCE in a particular class.

I don't think you are ready in the design to create composite indexes to speed up processing. But they are most useful when used in conjunction when they contain multiple common search columns. The idea is to help the query engine to pick an index rather than use a full table scan to find records. One of the decision points is how many values a field has. So if you had a field named Gender, it would have 3 values (or perhaps 50 these days if you are uber politically correct). Three values wouldn't be enough to catch the query engines attention so it would simply do a full table scan but if you included it with city and account type, the query engine might use that index if you are searching on at least two of the three values.
 

Mark_

Longboard on the internet
Local time
Today, 02:37
Joined
Sep 12, 2017
Messages
2,111
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

Registered User.
Local time
Today, 17:37
Joined
Nov 27, 2017
Messages
107
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).
 

Users who are viewing this thread

Top Bottom