Hello,
I'm trying to help my parents get out of the stone-age. My parents own a retail outerwear shop and they do everything the old fashioned way, by hand. I'm trying to set up a simple database to hold all their transactions so later they can doo lookups and A/R and A/P reports, etc, etc. First let me show you what I have so far:

http://www.humansky.com/images/db_screenshot.jpg

I'm stuck in how to set up the products table. They have inventory plus they perform services, like remodeling, repairs, and storage. So I was wondering if I should set up 3 seperate databases, one for inventory, one for repairs/remodeling, and another for storage or just have one giant products table with storage being one row, repair/remodelling another row, and the inventory taking up the rest of the database. Before I go on, I was wondering if anyone had any experience with such a retail database and what they recommend and if there are any pitfalls I should lookout for. Any input would be much appreciated by this n00b database designer. Also if you recommend changing any of my current tables or will they do for now.

Thanks,
Henry

First comment - you'll probably want a separate ORDER and ORDER_LINE table. If someone buys multiple items, you don't want to have to insert several rows in the ORDER table.

Whether to split the PRODUCT table in 2 or 3 depends on how many fields they share and how you want to use your constraints. For instance, if a service has a bunch of fields that don't apply to inventory products, you're going to have NULLs for 99% of your rows. I find that ugly.

What I typically do is put the common fields into a PRODUCT table, and then have separate tables that share the same key with a 1-to-1 relationship. ie. INVENTORY and SERVICE tables defining those "types" of products.

But it really depends on how complex you want your model to get, if it's just a very simple app for a very simple store, that can often be overkill.

The Database Answers web site has a few Inventory-related Databases that you might find interesting. You could also look for a Customer-Order Database and combine tables that look relevant to your problem.

Check it out :-
http://www.databaseanswers.org/data_models/index.htm

HTH

B.Dimple
Senior DBA