Database design best practice

[Joebro]

Hello,

I'm after some advice on how to best design a product database project. Currently, I'm managing this in Excel, which is complex, cumbersome, and not user-friendly.

Basic project explanation:

Every week we receive a vendor product list (CSV file) that contains updated product info (new products, unit cost etc). This Vendor CSV file will be used to update our product db. Our updated product db is then used to update product data in Shopify via a CSV file. The plan is to simplify the current process by moving from Excel to Access where the aim is to generate the Shopify CSV file more simply.

Scope of Project:

The main components to the product database project as I see it:

1. CSV import of Vendors updated product data into our db, this currently ends up in our ProductTable via some basic queries ( pretty straightforward)
2. Our ProductTable is where we manage all our products i.e set margins, update pricing, specials, categorize products etc... There are currently 1700 products in this table.
3. Generate a Shopify CSV file from data in our ProductTable. This requires extensive text string manipulation from fields in the ProductTable

I currently have a handle on 1 & 2 that I developing using VBA/form, the question I have relates to how best to generate the Shopify CSV file. My options as I see it are ( and please provide other/better options if they exist):

(a.) Include all the Fields of the Shopify CSV in the ProductTable. Once the vendor's data is updated in the ProductTable, do all the string manipulation in the one table, then do a simple query to select and generate the required fields of the Shopify CSV file.

(b.) Once the vendor's data is updated in the ProductTable, generate a separate ShopifyTable ( in the format of the Shopify CSV file) from the ProductTable use queries to manipulate strings to populate the ShopifyTable in the required field format. Then export the data in ShopifyTable to generate the Shopify CSV file and delete ShopifyTable prior to the next update run

Option (a.) for me seems easiest as it more excel-like to develop. But I think (b.) is probably the way I should go? Thoughts?

Cheers
Joe

 

[theDBguy]

Hi Joe. In Access, a table and a query pretty much function as the same thing. Depending on how complex the Shopify fields are, seems to me you can just leave the products table alone, create a query to represent the Shopify fields and then export that query into a CSV file.

 

[The_Doc_Man]

I'm going to give you my stock answer in multiple parts. The Old Programmer's Rules. (I'm qualified 'cause I'm an old programmer.)

Rule 1. If you can't do it on paper, you can't do it in Access.

This rule means that you have to do sufficient pre-coding analysis to determine what you will do in various situations. You need to lay out your data tables and simulate data flow to decide "what to do next." You take notes at each action step. Joe, you showed us that you have only posted very few times. I didn't see your intro, so I have no idea how familiar you are with Access, but you CLEARLY suggested that you WERE doing this via Excel. If you don't know anything about database normalization, DO NOT WRITE ANY CODE YET. Stop and veer into some web articles on the subject. Do a search of THIS forum (SEARCH is upper right, in the menu bar) for "Normalization" OR do a web search on "Database Normalization" (because the word normalization is used in not less than six different disciplines). Learn about table layouts. Learn about data relationships. THEN come back and start designing tables. If you have a decent-sized dry-erase board and suitable markers plus some decent-sized pads of sticky notes, you can lay out tables in a visual manner and explore their relationships.

Once you have reasonably normalized tables, you can start "simulating" your business data flow. Remember that you are building a programming model of what your business does. Take WRITTEN notes along the way. Design what you want to see, including appearance and content. Once you have the process mapped out on paper, you have a guidebook on how to implement it in Access. If you can't do that on paper, you are not ready to do it in Access. Unless you like going back a correcting your code dozens of times. Think of this set of notes as your roadmap. If you are on a trip in unfamiliar territory and don't have a roadmap, how will you EVER get to where you wanted to go?

Rule 2. Access can't tell you anything you didn't tell it first, or at least tell it HOW to find what you wanted to see.

Remember, Access is a User Interface and Database Engine in a box. It knows how to build tables, queries, forms, reports, macros, and modules. It knows how to express relationships. When it comes to real-world problems, though, it is dumber than a box of rocks. YOU are going to be the subject matter expert in this partnership. So.... once you have that written set of guidelines from #1 above, check all of the outputs you expect to see. Verify that you have sources for each of them. Which means if you wanted to see W in a report, you need a source of W in your input. If you wanted to see XYZ in a report, you have to have sources of X, Y, and Z and a formula to combine them appropriately. If this means stepping backwards through your processes to verify data availability, so be it.

Rule 3. When the real world says X and the database says Y, the database is USUALLY wrong.

The issue with using a database implementation to track/support/manage your business is that the business was there first. Your business procedures and data flow procedures were there first. If the DB disagrees, it is POSSIBLE - but HIGHLY UNLIKELY - that you found a flaw in the business rules. It is FAR more likely that you found a flaw in the database's implementation of those rules. Stated another way, NEVER let the DB make you do something that is contrary to the business rules. NEVER let the tail wag the dog.

 

[Pat Hartman]

Without knowing the requirements for a Shopify file and why it needs so much string manipulation, it is hard to tell you what to do. Generally, if the values don't change once the data is imported, I would "calculate them at that time and save them.

The only benefit to a separate table would be if it were possible to save the "calculated" values and not have to replace them each time you get a new price list from a vendor but that doesn't sound reasonable to me. So, you might as well keep them in the product table itself.

 

[Cotswold]

Rule 1. If you can't do it on paper, you can't do it in Access.

This is more or less what I would say to new clients and a simple way to explain things.
I'd say to prospective clients "If you cannot write down what you want, then I can't sit down and write the program you need"
Of course they'd never ever cover everything but I didn't expect them to. After all they aren't analysts and have little understanding of the problem. But their input and knowledge of their business is important at the start. Mind you. always scope for extras but after a while you get a good idea of where they want to end up.

It is always surprising just what some guys don't know.
One who ran a reasonable sized business in waste recycling, always in new Mercs and with a helicopter, asked me to train and explain how his software worked but out of hours and not to tell his staff about it. At one point I said, "Now Jim when the program starts, this is the default main operations screen" He then said "Don't say words like default, I don't know what they mean" At another client, a few years after installing a fairly complex system and having added a series of extra features, the owner turned to me and said " It is very good but I'm worried that we may have used up all the tricks in the software and at some stage soon you cannot add any more" I assured him that there was still a huge capacity for 'more-tricks'

If Michelle, or anyone else follows the advice from TDM, they'll not go far wrong.

 

[Isaac]

I always tell people, if we can define it we can design it. And the definition starts by being able to write it. Sadly that's a decreasing capability in modern society as everyone wants to show you something instead of tell you something, mostly because their ability to tell has decreased significantly over time and with the Advent of cool visual technology.

They love to have meetings which is fine as long as when I do their job for them and write the requirements at the end of the meeting everyone agrees that they will be built according to those requirements not according to the meeting!