UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How's My Relationships Diagram Looking?, Access 2016    
 
   
Art_Of_War
post Dec 7 2017, 04:13 AM
Post#1



Posts: 12
Joined: 4-December 17



Preamble

Hi all, I am designing an Orders and Accounts database for a client who owns a music store. Today he had a look at my database and gave me the feedback I needed to restructure it a little bit to accommodate a Supplier table.

ER Diagram

Here's the Relationships diagram:

Attached File  Relationships_7Dec17.JPG ( 62.01K )Number of downloads: 25


Let me know what you think, it works well in practice (I've designed all my forms and reports) but there could be a few things I'm missing.

Questions...

For example, will my client need the Supplier details to be a) all-inclusive (of address, city, state etc.) or b) simple (e.g. just the Supplier name, phone, mobile and email)? I get the sense that he wants the latter, and perhaps, if that is so, I can cut down the Client table so that it only contains the bare minimum of fields, i.e. FName, LName, and Phone/Mobile. He hasn't specified whether he will want to manually enter all the Client details into the database, including Address, City, State, Postcode, Phone, Mobile, Email, and again he comes across as wanting to enter the bare minimum of data into this database for it to run smoothly and do what he needs it to do:

The Crunch

a) place Orders from Suppliers, and
b) keep Accounts for Clients.

Kind regards,
Art_Of_War.
This post has been edited by Art_Of_War: Dec 7 2017, 04:23 AM
Go to the top of the page
 
Jeff B.
post Dec 7 2017, 08:38 AM
Post#2


UtterAccess VIP
Posts: 9,880
Joined: 30-April 10
From: Pacific NorthWet


Maybe I just missed it -- you appear to have products that come from suppliers, and customers who have accounts. Where do you model what products customers buy?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
mike60smart
post Dec 7 2017, 08:45 AM
Post#3


UtterAccess VIP
Posts: 12,350
Joined: 6-June 05
From: Dunbar,Scotland


Hi

I agree with Jeff

A Customer/Client will Order a number of Items/Products

Items/Products are supplied by a Supplier


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Art_Of_War
post Dec 7 2017, 08:46 AM
Post#4



Posts: 12
Joined: 4-December 17



No need, that's done in person at the cash register... my client just needs to be able to keep track electronically of what he does on paper, namely, (painstakingly long) Order forms, and Accounts for people who, say, borrow money or put down deposits on musical instruments or what-have-you. It's going to help him out a lot.

If he asks me to implement further functionality that will involve adjusting my relationships, then I'll go right ahead and maybe consult you guys further, but at the moment it seems like it's not really a requirement at all.

Just need to sharpen my VBA skills now, they leave a lot to be desired.
This post has been edited by Art_Of_War: Dec 7 2017, 08:48 AM
Go to the top of the page
 
GroverParkGeorge
post Dec 7 2017, 08:53 AM
Post#5


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


"No need, that's done in person at the cash register..."

So, you're saying that there is no need to track sales of products in this database? That would mean, I guess, the inventory is just going to continue to grow indefinitely because new orders are received from suppliers, but no sales against those products are ever going to be recorded?

An ever-growing inventory is probably NOT what the client would expect, IMO. Maybe you need to invest more time understanding the purpose and use of this database so there are fewer surprises along the way.

--------------------
Go to the top of the page
 
doctor9
post Dec 7 2017, 09:53 AM
Post#6


UtterAccess Editor
Posts: 17,913
Joined: 29-March 05
From: Wisconsin


Art_Of_War,

Just a minor quibble with the Orders-specific tables - under tblProduct, you have "Article" as the field that describes the thing that the table is a collection of. Why not "Product" instead, for more consistency? (Or tblArticles?)

Without knowing more about the business, it's also interesting that there is no cost associated with orders. There's a quantity, but no price, which I would think would be important. Is this just an inventory database, or will you need to track how much is spent on orders as well?

In regards to the Accounts-specific tables... It looks like you've created a many-to-many relationship between Accounts and Clients, i.e. One account can have many clients and one client can have many accounts. Is that an accurate description of the real world data, though? If I buy a piano in October, then a guitar in December, do I have two separate "accounts"? I'm afraid the terms "Particulars", "Folio" don't help me to understand what the tblAccount table is storing. I do recognize separate Debit, Credit and Balance fields, which is a common mistake when dealing with accounting, IMHO. Debits and Credits should be stored in the same field. Debits are positive, Credits are negative. When you store them in one field, you can CALCULATE the balance by summing the values. You don't want to store the balance - it's a number that's the sum of the debits and credits. That makes sense in Excel, but not so much in a database. Also, instead of storing debits and credits in an Accounts table alongside the "Particulars" and "Notes", you should have a separate table for the payments/credits that are related to that one account.

Generally speaking, don't be afraid to make your field names longer. "SID" is not as easy to understand as "SupplierID". Shortening these names doesn't really improve database performance, especially when dealing with a small business model.

Hope these notes help you out,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Art_Of_War
post Dec 9 2017, 10:33 AM
Post#7



Posts: 12
Joined: 4-December 17



Ok, I just renamed the "Article" field to "Product"--thanks Dennis for pointing that out.

"There's a quantity, but no price, which I would think would be important. Is this just an inventory database, or will you need to track how much is spent on orders as well?"

Yes, I find it interesting myself. I asked him, and he said that there was no need to indicate pricing; he wants a very simple database whereby he can enter Product names and just Send it off via email. I've created the correct forms and reports to this end. Can't show you guys at the moment because I'm using my Linux computer instead of my Windows one, but I will post some pics in due course.

"In regards to the Accounts-specific tables... It looks like you've created a many-to-many relationship between Accounts and Clients, i.e. One account can have many clients and one client can have many accounts. Is that an accurate description of the real world data, though? If I buy a piano in October, then a guitar in December, do I have two separate "accounts"? I'm afraid the terms "Particulars", "Folio" don't help me to understand what the tblAccount table is storing. I do recognize separate Debit, Credit and Balance fields, which is a common mistake when dealing with accounting, IMHO. Debits and Credits should be stored in the same field. Debits are positive, Credits are negative. When you store them in one field, you can CALCULATE the balance by summing the values. You don't want to store the balance - it's a number that's the sum of the debits and credits. That makes sense in Excel, but not so much in a database. Also, instead of storing debits and credits in an Accounts table alongside the "Particulars" and "Notes", you should have a separate table for the payments/credits that are related to that one account."

Well, what I did was, just wholesale copied from the sample Accounts form he gave me (in paper)... I could certainly do those parts you've suggested. The great thing about Access is that it's relatively easy to change things, it doesn't require wading through heaps of SQL code, though I don't mind doing the latter either really. I could certainly implement all the changes that you guys are willing to suggest.

Thanks everyone for posting,
Leo C.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 08:00 PM