Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Tables + Relationships _ How's My Relationships Diagram Looking?

Posted by: Art_Of_War Dec 7 2017, 04:13 AM

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:



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.

Posted by: Jeff B. Dec 7 2017, 08:38 AM

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?

Posted by: mike60smart Dec 7 2017, 08:45 AM

Hi

I agree with Jeff

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

Items/Products are supplied by a Supplier


Posted by: Art_Of_War Dec 7 2017, 08:46 AM

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.

Posted by: GroverParkGeorge Dec 7 2017, 08:53 AM

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

Posted by: doctor9 Dec 7 2017, 09:53 AM

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

Posted by: Art_Of_War Dec 9 2017, 10:33 AM

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.