MySQL Forums
Forum List  »  Newbie

Question about Database Structure
Posted by: Simon Maltby
Date: February 09, 2017 04:25PM

First of all I should let you know that I am a complete novice, and have never formed a database before (except in Access but that doesn't count).

I will tell you what I have done and what I would like to achieve and know:

I have a server (Windows), running Apache and MySQL. This bit I can do easily.

My grand plan is to keep track of parts I sell. About 50,000 different types of parts. Every part has a unique serial number, and every part has a Manufacturer and a part number. Generally speaking there are only up to about 20 of each part at any one time. I deal in 2nd hand spares. Very few new parts.

Parts:
1. My Part Number – Varchar(20)
2. Part Name – Varchar(20)
3. Manufacturer – Varchar(20)
4. OEM Part Number – Varchar(30)
5. Alternative Part Numbers – Varchar(256)
6. Location on the shelf – Varchar(30)
7. Stock Quantity – Int(10)
8. Low Stock Level – Int(10)
9. Used On – Varchar(256)
10. Key Word – Varchar(256)

Alternative Part Numbers, Used On and Key Words are to be searchable strings.

Serial Numbers:
1. Serial Number – Int(10)
2. Condition – Enum(‘New’, ‘Used’, ‘Unknown’)
3. Location – Varchar(20)
4. Return Status – Enum(‘None’, ’Pending’, ‘Creditable’, ‘BER’)

Location at this point is either Stock or a Customers name (not interested in a Customer database at tis point).

History:
1. History – Varchar(256)
2. Time & Date – Datetime

The History is the history per Serial Number. There may be may returns and send outs of the same serial number part.


Now here is my question - it will sound really simple and easy but I know if I start out on the wrong foot I will have to start all over again:

The structure of the Part Number is simple. The structure of the Serial Number is simple. The History part is the one which I haven't yet got my head round.

Should I do this, or is it wrong:

Create 3x Tables, called Parts, SerialNo, History.
Within the 3x Tables, enter the Fields as stated above.
Use the (not mentioned) ID Field to form a relational link from History to SerialNo, and same again from SerialNo to Parts?

It is as simple as it sounds, but like I said I haven't yet got my head round it yet.

Options: ReplyQuote


Subject
Written By
Posted
Question about Database Structure
February 09, 2017 04:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.