my first post so be gentle with me 
Edit: sorry if its a mess.
right im trying to design a database for a small business selling quad parts (just doing the parts side for now) because of the nature of the business we dont store any products ourselves, we order from our suppliers by phone as we get the orders...
so the things i need it to do are:
the thing im stuck with on my layout is the order history bit, the way the business works at the moment is when we get a call asking for a part we then call suppliers and find the cheapest part we can. im hoping from usage to build up enough data for the database to give estimated prices by averaging the unit price of an item over a time period chosen by the user....
the tables iv got so far (not confident with what i have atm)
Customer:
Customer ID - pk
First Name
Last Name
Email
Phone Number
Address1
Address2
Address3 (im using 3 fields for address so i can search for customers in an area, can this be done only using 1 field ?)
Post Code
Notes
Quad:
Quad ID - pk
Customer ID - fk
Quad type - fk
quad make
quad model
quad year
engine size
Quad type:
Quad type ID - pk
quad type
User:
user id - pk
user name
Quotes: (9 out of 10 orders will start with a quote but not all
)
quote id - pk
Customer id - fk
user id - fk
quad id - fk
qitem desc ID - fk (multiple items in the same quote)
quote notes
quote date
item desc: (quote)
qitem desc ID - pk
estimated price (not sure how to make this work)
quantity
second hand - (yes/no)
Orders:
Reference Number - pk
Customer ID - fk
Quad ID - fk
User ID - fk
Sub total
Total
Date sold
ItemDescription: (Orders)
Desc ID - pk
Reference Number - fk
Item Description
unit price
Quantity
second hand (yes/no)
Invoice:
invoice id - pk
reference number - fk
customer id - fk
quad id - fk
user id - fk
invoice date
due date
thats all the tables iv made at the moment, and the relations between them... i havnt included the order history table there because i havnt a clue what should be in it.... from what iv read this seems like a naughty thing to say...but id like to have calculated fields saved into the order history XD
iv never designed anything this complicated before, so im a bit out of my depth...hopefully you guys (and girls) can understand nonsense and point me in the right direction
thanks for your time,
Alex
Edit: sorry if its a mess.
right im trying to design a database for a small business selling quad parts (just doing the parts side for now) because of the nature of the business we dont store any products ourselves, we order from our suppliers by phone as we get the orders...
so the things i need it to do are:
- hold customer details
- hold quad details for each customer (usually 1 quad per customer)
- Hold record of orders
- simple user interface (not IT people im designing for
) - show quarterly sales
- identify user
- input data from orders to an order history table
- Search for estimated prices from order history
- show active orders
- search by category + search for specific customer
the thing im stuck with on my layout is the order history bit, the way the business works at the moment is when we get a call asking for a part we then call suppliers and find the cheapest part we can. im hoping from usage to build up enough data for the database to give estimated prices by averaging the unit price of an item over a time period chosen by the user....
the tables iv got so far (not confident with what i have atm)
Customer:
Customer ID - pk
First Name
Last Name
Phone Number
Address1
Address2
Address3 (im using 3 fields for address so i can search for customers in an area, can this be done only using 1 field ?)
Post Code
Notes
Quad:
Quad ID - pk
Customer ID - fk
Quad type - fk
quad make
quad model
quad year
engine size
Quad type:
Quad type ID - pk
quad type
User:
user id - pk
user name
Quotes: (9 out of 10 orders will start with a quote but not all
)quote id - pk
Customer id - fk
user id - fk
quad id - fk
qitem desc ID - fk (multiple items in the same quote)
quote notes
quote date
item desc: (quote)
qitem desc ID - pk
estimated price (not sure how to make this work)
quantity
second hand - (yes/no)
Orders:
Reference Number - pk
Customer ID - fk
Quad ID - fk
User ID - fk
Sub total
Total
Date sold
ItemDescription: (Orders)
Desc ID - pk
Reference Number - fk
Item Description
unit price
Quantity
second hand (yes/no)
Invoice:
invoice id - pk
reference number - fk
customer id - fk
quad id - fk
user id - fk
invoice date
due date
thats all the tables iv made at the moment, and the relations between them... i havnt included the order history table there because i havnt a clue what should be in it.... from what iv read this seems like a naughty thing to say...but id like to have calculated fields saved into the order history XD
iv never designed anything this complicated before, so im a bit out of my depth...hopefully you guys (and girls) can understand nonsense and point me in the right direction
thanks for your time,
Alex
