Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

Thread Tools Rate Thread Display Modes
Old 11-30-2017, 11:31 AM   #16
Longboard on the internet
Join Date: Sep 2017
Posts: 1,083
Thanks: 13
Thanked 203 Times in 201 Posts
Mark_ will become famous soon enough
Re: Accounts and Orders DB

Originally Posted by Leo_Coroneos View Post
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?

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.


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_ is offline   Reply With Quote
Old 11-30-2017, 11:31 AM   #17
Pat Hartman
Super Moderator
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,915
Thanks: 13
Thanked 1,314 Times in 1,253 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Accounts and Orders DB

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.
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 12-01-2017, 11:48 AM   #18
Longboard on the internet
Join Date: Sep 2017
Posts: 1,083
Thanks: 13
Thanked 203 Times in 201 Posts
Mark_ will become famous soon enough
Re: Accounts and Orders DB

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.

Mark_ is offline   Reply With Quote
Old 12-03-2017, 08:24 PM   #19
Newly Registered User
Leo_Coroneos's Avatar
Join Date: Nov 2017
Location: Albany, Western Australia
Posts: 111
Thanks: 19
Thanked 1 Time in 1 Post
Leo_Coroneos is on a distinguished road
Re: Accounts and Orders DB

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).

Database Design Solutions
Databases for the 21st Century
Leo_Coroneos is offline   Reply With Quote

access , accounts , diagram , orders , relationships

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Entering orders into the orders table megatronixs Modules & VBA 9 03-07-2014 02:52 AM
[SOLVED] Accounts Receivable carlc Tables 1 08-01-2005 09:23 AM
Chart Of Accounts? mohammadagul General 9 10-29-2004 12:07 PM
User Accounts k209310 General 2 01-29-2003 12:50 AM
Northwest Orders DB Orders Form RootsCorner Forms 0 03-03-2002 11:16 AM

All times are GMT -8. The time now is 03:02 PM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit

Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World