franchisee setup tables

i am trying to set up a database to record details of a suit rental hire franchise. I am a bit of a novice but the tables i have constructed so far are as follows:

tblcustomer

customerno (pk)
title
forename
surname
address 1
address 2
address 3
address 4
postcode
telephone

tblorders

order no (PK)
order date
staff no
customer no
customer name
franchisee no
function date
suit no
waistcoat no
shirt no
bow


tblmeasurement

measurement no (PK)
order no
customer name
jacket size
waist size
inside leg
collar size
special needs

tbldeliveries

delnote no (PK)
order no
franchisee no
customer no
suit no
waistcoat no
shirt no
bow staff no

tblstaff

staff no(PK)
title
surname
forename
position
salary
adress1
address 2
address 3
postcode
telephone

tblstock

stock no (PK)
description
size
starting stock
price
low stock level

tblinvoice

invoice no (PK)
customer no
date

tblinvoice_detail

invoice detail
invoice id
stock no
quantity
price

tblfranchisee

franchisee no(PK)
company name
address1
address 2
address 3
address4
postcode
telephone no
e-mail


do the above table make sense and be able to form proper relations

The first thing I would query is whether tblOrders is normalised. The problem with the current structure is that you need to keep adding columns for different items.
Suggest:
tblOrderHeader
OrderNo (PK)
CustomerNo
FranchiseeNo

tblOrderDetail
OrderDetailNo (PK)
OrderNo (PF)
ItemNo (FK)

tblItems
ItemNo (PK)
ItemType
ItemDescription
ItemSize

Next point, you should not store customer name in tblOrderHeader (or in your case tblOrders). Customer name is in tblCustomer and linked by CustomerNo.

Next point, the way you've structured suggests that a measurement is associated with an order. I guess that makes sense rather than being associated with a customer since the customer's size will change. So your CustomerNo (FK) looks fine in this table. However, the measurements again scream out being put into another table just like I did with the order header/ order detail. e.g.

tblMeasurementDetail
MeasurementDetailNo (PK)
MeasurementType (indexed, no duplicates)
MeasurementValue
MeasurementValueUnitOfMeasure

Same goes for tblDeliveries i.e. it needs two tables

Next point: Stock is a challenging topic. Do a search of these forums for best practice on how to implement stock in a database. But typically I think you should be thinking along the lines of a transactional table that lists item out, date time, item in date time etc. You then sum up the out and ins in a query to give you a stock balance - you don't actually store the stock balance.

I would guess that invoice is really related to a delivery - not a customer?

Also, avoid using spaces in your field names as it will give you a headache at some point.

Hope that helps. Do read up on normalisation if you haven't already.
Chris

on tblorders the stock items are listed in a drop down list from which the person placing the order selects the required one. each one has an individual name. are these lifted from the stock table?

For measurements, there is a lookup there also for all measurements available. Do I keep those?

in the stock table each one of the items is autonumbered with the description if each item

"MeasurementType (indexed, no duplicates)
MeasurementValue
MeasurementValueUnitOfMeasure"

what type of entry would you put against those? Ideally the measurements are Chest, Waist, Inside Leg and Collar. also to create a relationship, should i place in measurementno as a foreign key?

harps said:

on tblorders the stock items are listed in a drop down list from which the person placing the order selects the required one. each one has an individual name. are these lifted from the stock table?

Click to expand...

As I mentioned, you don't really need a stock table. That's not to say you can't have one. It's just that maintaining a stock table is much more challenging to implement and keep in sync with your transactions. Instead you just sum up your incoming and outgoing transactions in a query. You can use this query to check what stock you have on hand.

harps said:

For measurements, there is a lookup there also for all measurements available. Do I keep those?

Click to expand...

If I understand you correctly then yes I would encourage the use of lookup tables for standard lists.

harps said:

in the stock table each one of the items is autonumbered with the description if each item

"MeasurementType (indexed, no duplicates)
MeasurementValue
MeasurementValueUnitOfMeasure"

what type of entry would you put against those? Ideally the measurements are Chest, Waist, Inside Leg and Collar. also to create a relationship, should i place in measurementno as a foreign key?

Click to expand...

Yes, Chest, Waist etc was what I was thinking for measurement type.

"measurementno" - do you mean MeasurementTypeNo? Yes I would encourage the use of a number to uniquely identify each measurementType.

Please note I'm only surmising how a suit hire business works and basing my suggestions on that.

Chris

Thanks for your time all this is extremely useful. I think what I am trying to get at here regarding stock is where would I keep my opening stock against which all hire and returns are set against. This would be a useful tool to know when i have reached a low stock level so there can be no instances of over booking.
The question about measurementno is more about how to get the relationship between the header and the measurementdetail table, what would be the foreign key.

Thank you for your patience

i have the tables done to what i hope are the correct fields and built the relationships, unfortunately the file is too big to attach

harps said:

i have the tables done to what i hope are the correct fields and built the relationships, unfortunately the file is too big to attach

Click to expand...

If you are wanting to upload your database then read here.
Chris

try this please

harps said:

Thanks for your time all this is extremely useful. I think what I am trying to get at here regarding stock is where would I keep my opening stock against which all hire and returns are set against. This would be a useful tool to know when i have reached a low stock level so there can be no instances of over booking.
The question about measurementno is more about how to get the relationship between the header and the measurementdetail table, what would be the foreign key.

Thank you for your patience

Click to expand...

I took a look at your d/b. To answer you question about stock, the opening stock is just another receipt transaction e.g. receive 12 x black jackets size 32.

Take a look here at an example of how stock is implemented as a series of transactions. [thanks to Gemma the husky for highlighting this link]. Do a search for more example.

Regarding measurements, I would expect your tblMeasurements to look more like this:

MeasurementID (auto)
OrderNo (FK)
MeasurementType
MeasurementValue
UnitOfMeasure

So a typical values might be:
MeasurementID OrderNo MeasurementType MeasurementValue UnitOfMeasure
1______________1________Chest______________40____________inch
2______________1________Leg________________32____________inch
3______________1________waist_______________34____________inch


You still need to address the points I made about having suit name, waistcoat number, shirt number, Bow tie as separate fields. They should be a single field in a seperate table e.g. ItemID.

Customer No, Staff No etc do not belong in the deliveries table. They are in the order header and therefore can be looked up via the order header using the Order No as the reference.

This all might seem a it of a mine field. The database you are tackling isn't the easiest example to get started with and you are having to deal with several concepts at once. But if you get the structure right then you'll have a good foundation for a good database.

hth
Chris

Hi again

here is the revised version if this seems ok, i would like to build the queries and the forms.
thx again your advice has been invaluable

Harps