?
Solved

Database Design Query for Computer Shop

Posted on 2003-11-04
5
Medium Priority
?
1,000 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 150 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

801 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