Book Shop Database Ideas help?

[June7]

Number of employees has nothing to do with how stock is acquired and tracked.



Slight revision to my comments about payments. I was seeing the structure as card payment for every order, then I noticed your 'payment type' field. These would be cash, cc, other? If you want to allow for none cc payment, consider:

1. Customers table has a record for customer named Cash

2. Customer Cash has one 'card', say number 9999 9999 9999 9999, security 999, card name Cash, start 1/1/2000, exp 12/31/2999

3. Orders table has field for customerID and maybe for CardID if child table for credit cards - still recommend card info in Customers table or a child table

I know you are doing this for coursework and want it to be simple so maybe just design for ideal world, not real world. What is ideal in your db world - everyone pays by cc, every customer has one cc that is never declined? Establish your assumptions and givens and design within those parameters.

Reality of db design is it is a balancing act between data normalization and ease of data entry/output. I ignore/break/corrupt many 'rules' for sake of convenience and to make my db do what I want.

[ilikeshinythings]

Yeah from studying Oracle I have realised Databases are never going to be perfect lol

and yeah well ideally I would like to have it that everybody pays cash so I have no need to keep a record of anything but at the same time I think it would be helpful to have an option to pay by debit card/credit card. Unless this would be hard to implement in which case i would only allow cash.

Ok it's 1:15am here so I think I should probably get some sleep lol

Thanks for the help so far!!!

[orange]

I'd still suggest the Price on the OrderDetails Table. see post #20 for rationale


The following is just for consideration
You could have an OrderPayment table Linking Payment to Orders to allow multiple payments per Order

with .....................................compound PK
OrderId (FK) of related Order
PaymentId
PaymentType
PaymentAmount
PaymentDate
other card and payment info as has been provided

and a PaymentType table to identify anything specific to a Payment

eg
(PaymentType, Description)
01 Initial Payment
02 InterimPayment
03 Final Payment


OOOPs again: I was typing and thinking while watching NCIS I was working from Page 2 didn't realize there was a 3 until I submitted.

[June7]

Orange, now go easy on the OP, you will make head explode!

As orange stated before, you would want to save price in OrderDetails only to allow for revising the book price in the Books table and not impacting existing order records. Saving it in OrderDetails will require either manual entry or code to grab from Books record. The alternative is a new record in Books table for each pricing of the book. So if you bought Moby Dick in January at 17.49 and set retail cost at 25.99 that would be one record. If you bought another copy of the exact same Moby Dick in August at 22.99 and set retail cost at 30.99 that would be another record.

[orange]

Agree, I wasn't suggesting a detail of Price changes -- only selling or agreed to Price in OrderDetails; and current/latest price in Books.

The OrderPayment was just multi payments per Order - but they could have a fact --all Orders are Paid by 1 payment (only 1 payment per Order)
They could even go with all Payments are Cash.

......

[ilikeshinythings]

Yeah I've added the book price to the order details table I totally forgot about that thanks.

I think I'm going to just leave the payment info in the customers table because its alot easier and i'm just assuming all customers will pay by card or cash and all payment types will be the same throughout all the customers orders.

Here is my ERD as it stands now. I am now going to start creating the database from this ERD:

For the data entry I think I will have a form for 'NEW BOOK', 'NEW CUSTOMER' and 'NEW ORDER' which will have order_details as a subform. I'll make it so that the 'Add New Order' button opens the New Customer form and once that is filled out and added the Orders form with the details subform will appear and then that will be filled out and added. But first i have to make the tables haha

[orange]

Sounds good.

I would identify (explicitly) any assumptions/conditions for your ERD
-customers make only 1 payment for order
-all customers will pay by card or cash and
-all payment types will be the same throughout all the customers orders.

That way you can put a boundary/scope on your project from a point of knowledge. That is it wasn't an oversight. It was planned specifically for these conditions. (And in the real world - if you want something more, that's additional work)

Good luck with your project.

[ilikeshinythings]

Yeah I will add it into my planning document or user documentation like the example in a previous post.

Thanks! and I'll probably be back when It comes to my forms ahah but atleast i'm getting somewhere now

[ilikeshinythings]

Ok so I've now created all the tables and linked them with the relationships (shown below).

I haven't tested any of the input masks or validation yet because when I test it the autonumber fields will go up even if i delete the record wont they? any idea how do I test them without ruining the autonumbers?



thanks

[June7]

Gaps in autonumber sequence should not be an issue. Users should not even be aware of the autonumber pk/fk. Don't show them to users.

[orange]

As June7 said autonumbers are for Access not users.
If after testing you want to reset, delete all test records and do a Compact and Repair.
It isn't an issue for Access, but if you want them reset, that's how it's done.

[ilikeshinythings]

Oh right okay and yeah I suppose if i use a form for orders and a subform for order_details theyll all match up too, thanks

[June7]

Related records of main and sub forms will synchronize when the Master/Child links properties of the subform container control are set.

[ilikeshinythings]

Hi sorry to bother you guys again but I've just built my database and I've created all the forms etc. but I am stuck on this one part of my form.

I have it set up so that you select the book you want from the 'Book_ID' combo box but the box below has 'Book_Price' and rather than the user entering this manually or selecting it manually from a list I thought I could use a statement to automatically look up the ID of the book which was entered into 'Book_ID' and then put the price of that book from the books table into the 'Book_Price' box on the form.

Is this possible?




Thanks

[orange]

Is your form working? If you select the Book_Id, does anything happen?

What is the recordsource of the Form?
What is the recordsource of the combobox?