Order DB

[adamcoppard]

Hi all,

I've had a go at making a relational database with Access,with the table structure below, but, I seem to be missing something. In the following design, all of the primary keys in the tables lead to a 1-many relationship with their fields in other tables, with the 1 relationship being on the primary side, and the many in the ORDERINFO table, or in the case of the carpets in ORDER(carpetid). The other thing I am stumped with, is generating a Form, or Report, that can reference each and every one of these Tables, and insert data into each of them (I'm currently trying to get a dropdown to display the carpetname, but, to actually insert the carpetid, and Microsoft haven't made it easy in 2007!).
This is my table structure:
ORDERINFO(orderinfoid, orderid, customerid, staffid, datetaken, fittingdate, fittingtime, finalcost)
ORDER(orderid, width, length, perimiter, carpetid, discount, gripperstrips, underlay, antistain, fitting, plywoodbase)
CUSTOMER(customerid, forename, surname, addressline1, addressline2, city, county, postcode, telephonenumber)
CARPETS(carpetid, carpetname, carpetcost)
EXTRAS(extraid, extraname, extracost)

Thanks for any help.

 

[gemma-the-husky]

it is normal to have 1 to many relationships - indeed thats usually the only relationships you should have

lets start at the beginning, and analyse the situation


typically you will have as a starting point

customers table
carpets table
orders table

now the orders table relates a customers order with the carpets he orders. so its

customers 1 - m orders
carpets 1 - m orders

---------
now the orderinfo table you mention seems to me to be additional things that OUGHT just to be in the orders table, and not in a table of their own - they would only need their own table if one order needed SEVERAL orderinfo lines

---------
the other issue however is multiple carpets - if a customer orders several carpets at the same time, then this way you will need to give EACH carpet a separate order. Alternatively you may want an ORDERLINES table to record the different caprpets (eg lounge and stairs)

so you might get

CUSTOMERS
CARPETS
ORDERS (linked to customers)
ORDER LINES (linked to orders - and linked to carpets)

--------
this gives a slight issue in that the fitting date is stored in the order, but the carpets are stored in an orderinfo table - so if a customer orders 2 carpets to be fitted on different dates, then you have a problem

the other issue is how to deal with the EXTRAS (underlay, grippers, fitting etc etc, I suppose) - you could either link these to the ORDER, or to the ORDERLINES - it depends which is appropriate for your circumstances.

--------
one other thing that arises is that your CARPET table may need to be treated as more of a GENERAL PRODUCTS table - to include in there UNDERLAY, GRIPPERS etc as well as carpets. (its geenrally easier to do it this way, and have a product group flag to distinguish carpets from extras, than have separate tables for each) You have also included COST in there (do you mean selling Price) - but you also need to allow for (discounted/special) selling price in the orderlines table - so you can change a standard price from £20/sq metre to £18/sq metre for a single customer - or even £500 for the job.

Don't forget that you carpet table will need to distinguish between different roll widths - do these have different carpet ID numbers, or are the roll widths distinguished in some other way? - with carpets if you include stock/inventory you will aslo have other issues in that presumably a lot of roll offcuts are insufficiently large to use, and have to be remaindered, so you may need to factor this in.


So overall, you can see that you really need to think how you ACTUALLY WORK, and structure your tables accordingly - dont try and change your business to accommodate a computer system. If you get this structure right, then everything else will flow naturally


Sounds like fun though

Hope this helps for a start


[re combo boxes

the practical issue with a combo box, is solved by having 2 columns, 1 with the ID, and one with the description - you make the width of the first column 0. The combo box trhen works by STORING the first column, (set bound column 1 in the data tab), but displaying the description (it actually displays the FIRST non zero width column) - the wizard will make these suggestions for you - but you may not have realized what it was doing. However, you do need a way of distinguishing the roll widths, as I explained previously]

 

[adamcoppard]

According to the specification order sheet I've been given, each customer can only have one carpet per order (or at least, that's what it look's like, and what a Excel based system uses as a rule of thumb).

I shall have a juggle with everything else, and see if I can build a form to enter all the information I want it too.

I'm also going on a bit of a learning curve by getting Access to do some calculations for me! Luckily, I have most of them stored in an Excel worksheet, so, I am hoping it should be similar formulae sturcture =[field1](mathematical operator)[field2] or such.

Thanks for the help, though.

 

[gemma-the-husky]

is this a school project then?

 

[Rabbie]

Remember it is not good practice to store calculated fields in your tables. Just calculate them on forms/reports when you need them.

 

[adamcoppard]

Correct, it is a school project. I hate the Access UI, so, may go ahead and do it in MySQL (I do know the theory, just the UI of Access is bad!)

That's what I was talking about the calculations, I was going to calculate them on the end of the report / form, where needed (it will actually only be needed on the end of the final report), which is why an area field isn't present, but final cost was transferred, after a mistake in the planning process.

I'll have a play with Access this afternoon, and may use PHP / MySQL at the end of the day. I've built the table, with all the relationships, and am about to build a form, to enter data into multiple tables.