Solved

Database Design Query for Computer Shop

Posted on 2003-11-04
5
876 Views
Last Modified: 2012-06-21
I have a query regarding the design of my database in MS Access
There will be parts sold and parts in stock.
Should i have 2 tables SOLD and IN STOCK?
OR should i have one table called STOCK with a YES / NO option under a field called SOLD?

 i will also be using this database for a tool hire program.

Say if a customer returns PART001, can i open a text file called PART001 and
add the customers name to the text file,so i have a history of who took the part and
then i dont need to clutter my database with this info
0
Comment
Question by:jxharding
  • 2
  • 2
5 Comments
 
LVL 46

Expert Comment

by:tbsgadi
ID: 9685029
How about this...
You Should have

1) Table :Parts
2) Table: Parts Movement -With CustomerID, PartID,Date ,No Parts,MovementType,
3) Table: Customers

MovementType being 1.add to Stock or 2.Sell etc

You certainly shouldn't use the text file...That's what the database is for!!
0
 
LVL 3

Expert Comment

by:jjjtuohy
ID: 9685482
This is a basic inventory structure.

1) Parts Table:  Parts_ID, Parts_Type, Parts_in_Stock

2) Customer Table: Customer_ID , Surname, First_name, Address1 etc

3) Transaction Code Table: Transaction_Code, Transaction_Type  (1 Sold, 2 Out of stock 3 Hired etc). This allows future flexibility.... you simply add new codes to this table.

4) Transaction Table: Transaction_ID , Transaction_Code, Date, Parts_ID, Customer_ID, Number_of_Parts, Comments

NOTE:
Parts_ID, Customer_ID, Transaction_ID in their respective originating table are {autonumbered primary key}

John
0
 

Author Comment

by:jxharding
ID: 9685749
thanks a lot,will be trying it today
0
 

Author Comment

by:jxharding
ID: 9687677
will i be deleting a part from the parts table if it is sold then and just move it
into transaction table?
0
 
LVL 3

Accepted Solution

by:
jjjtuohy earned 50 total points
ID: 9693224
Parts table: The Parts_in_Stock field  will decrement as you give out stock and increment as you bring in new stock. It is simply a count of current stock.
Transaction table: There is no moving stuff over or anything like that. the transaction table is precisely that.... a full historical record of every transaction undertaken. From it you can extract the information you require eg All the records relating to a particular customer, all the records in a particulat time period, all records relating to a particular part etc.
John
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Combobox row source 2 20
Trying to open FORM in specific record !! 6 44
Filter a form 8 14
My SQL as Backend for Access 3 14
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question