Beginner Needs Help Building Database 1

[fictionwelive]

Hello all,
My name is Justin and I work for a company in connecticut that sells Point of Sale Systems to restaurants. My father runs the company and I work here when I'm not in school, learning the ropes of the business and what not.

Before I get into my Access situation, let me describe a little bit about what we do.

We sell Point of Sale Systems to restaurants all over connecticut (Not chains such as Rubys, Fridays, Hops, etc.)
If you dont know, a POS system consists of 1-8 (or more) terminals and a fileserver. The servers/bartenders clock in on the terminals and all sales reports and such are taken on the server. We get all of our equipment (Computers, Touchscreens, Printers, etc.) from several companies, we do not build our own PC's.

A basic terminal consists of a "Booksize PC", a Flatpanel or CRT touchscreen, and a local printer.

When these things break, however, we have to replace them with a loaner item of the same type, and then bring the broken unit in house to fix or expedite to another company.

Basically, I am trying to create an inventory system of some kind through access.

Right now, I have four tables.

First one is the customer table:
Customer ID (Auto#) (Primary Key)
CustName
CustCity
CustState
CustZip
CustPhone

Next is the Supplier table:
Supplier ID (Auto#, Primary Key)
Supplier Name
Supplier Address
Supplier City
Supplier State
Supplier Zip
Supplier Phone

Next, Products:
Product ID (Auto#, PK)
Product Name
Product Cost
Product Retail
Supplier Name

Lastly, the inventory table is where Im having the most trouble. I want to be able to input every single item we get in into the database, and know where it is. (until now, our system has been all paper, a true pain.)

But I'm not sure If i should split that into two tables, or make it into one, for instance:

Product S/N (Primary Key)
Product Name (dropdown list from the product table)
Purchase Order (who the product wsa intended for, restaurant name from the restaurant table)
Current Location (Restaurant name from restaurant table, sometimes they dont always go where they were intended)
Status (Loaner Unit, In Stock, Being Prepped, etc.)
Notes

Or should I split that into two tables, one for striclty all inventory (Just Prodcut S/N and Name and Purchase Order), and then one for the location and status of product?

ANYTHING you can help me with, tips, suggestions overall woul be greatly appreciated.

Thanks in advance,
Justin Lee

 

[grande]

I'd keep it as one table. From here, all your stuff looks pretty good, and well normalised.

-------------------------
Just call me Captain Awesome.

 

[solun]

IMO - two tables. I would also suggest that you broaden the scope of the db so you track each piece of equipment you've sold and/or support, where it is, who made it, and each support ticket against it. Then you can (among other things) figure out which pieces end up being the most problematic - something you'd only be able to tell if you know the full population and the ratio of working to repaired pieces. It will end up being a sales analysis tool as well as a support/trouble db.

Good luck, Justin!

Heidi



Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com

 

[fictionwelive]

Really...

I was almost sure all of the Access Wizards would tell me to split it up.

Why would you suggest one, just out of curiousity

 

[grande]

Actually, after reading solun's recomendation, I'm gonna agree with him/her.

Two tables is far better, because you can analyse the data in more ways.

Give that (wo)man a star!

-------------------------
Just call me Captain Awesome.

 

[fictionwelive]

Alright, two tables it is.

I need help with the relationships, though.

Primary keys, foreign keys, skeleton keys, one to many, many to one, one to one, many to many, I'm not sure how to manipulate the relationships correctly so the database works for us...

Would someone lend a helping hand?

 

[fictionwelive]

Ok, the final table has been split into two as follows:

tblInventory:
Product SN (PK, serial number of product)
Product (from drop down list, links to product table)
Purchase Order Number
Date Recieved

tblLocation:
Product SN
Product Location (Restaurant name or In house, restaurant name from drop down list linked to customer table)
Product status
Notes

also I added a RMA table (Return Merchandise Auth.), so I can track how long certain units have been out to our different suppliers for repairs.
tblRMA:
RMA Number (PK)
Product SN
Date Shipped
Problem wiht unit
Notes

Can Product SN be the Primary Key for both tblLocation AND tblInventory? If not, what woud you suggest?

Hows it lookin so far?

If possibly, I would like to attach a screenshot of my relationships view, so that you can see the structure thus far.

 

[solun]

Thanks for the stars! (I haven't come across any male Heidis)

There's a faq 'round here somewhere for keys isn't there? It's probably in the table forum rather than the forms forum.

you've already got your pks defined correctly. foreign keys are just primary keys that belong to a table other than the one you're in right now.

A one-to-many relationship: each customer probably has a bunch of parts. thus one-to-many.

You'll have a table for customers, a table for parts, then a table for customer's parts that links customers with their parts. the customer table will have a one-to-many relationship with your customer's parts table. AFAIK, no skeleton keys ;-)

One-to-one relationshps are more rare. I use them to deal with data that, for example, some customers have but others don't, but each customer that does have that information will only have record in that table, thus one-to-one and it keeps your db size down because you aren't reserving space for that info for each customer.

Stupid example: each customer has a birthday but not an anniversary. the birthday can go in the customer data table, but the anniversary data can go in the anniversary data table with the one-to-one relationship based on the PK of the customer.

Enjoy!


Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com

 

[grande]

Oh... Sorry Heidi, I just read "solun".

-------------------------
Just call me Captain Awesome.