Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Database Design Query for Computer Shop

Posted on 2003-11-04
5
Medium Priority
?
1,054 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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 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…

610 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