Book Shop Database Ideas help?

[ilikeshinythings]

Hi all, I've got an assignment to do and the scenario i created was a book shop which is transfering from a paper system to an access database. They wouldn't need to copy over all their data just enter new data from the creation of the database onwards but I have come into a few problems in my planning.



I am used to SQL and PL/SQL programming in which it is alot easier to use fk constraints etc and it has left me somewhat confused about how i'm going to be able to do this task.

Here is what I have at the moment anyway, any help or guidance would be appreciated.

I was thinking having a main page which opens up when the user starts the database with a 'NEW ORDER' button which will open a form which will have order date and postage date from the Orders table, customer information fields from the customers table and payment information from the payment table, I will also need to get the book title in there somehow too. But what I am confused about is how can I get all of this information on one form? because otherwise the user would have to enter new customer then new order then new payment all seperately which is time consuming and not really what i want.

Also, i have an ID field on all of the tables (customer_ID for example) and I have to manually enter these when they are foreign keys, how does this work when it links to the same column of the table which it is a primary key (as in book_id in the books table is autonumber and works fine but in the orders table as its a fk i have to put the number in myself which i have to find out which it matches too which surely isnt right?)

Sorry if this doesn't make alot of sense but any questions or if you would like me to elaborate then please just ask

[June7]

A principal of relational database is to not duplicate data. So instead of one big flat file table with everything in it and having to repetitively enter info like customer name, address, phone, as well as all the details about purchase, related tables are used. This means customer record must exist before can sell to customer, merchandise record must exist before can sell item. So if you open form to sales record and want to select customer for that sale but customer not in data, then must handle that situation by opening customer entry form, add record, update data, return to sales form and select the newly created customer. Alternatively, open form to customer record, this form has a subform for sales records. If this is new customer, move to new record to enter customer info on main form then the sales record in the subform. The subform container will keep the related records synchronized so only that customer's purchase records will display in the subform and the customerID fk will automatically save to the sales record. You still have to select the item the customer is purchasing.

You should find tutorials at http://datapigtechnologies.com/AccessMain.htm interesting especially the ones about comboboxes, subforms, and searching/filtering records on form.

And this http://forums.aspfree.com/microsoft-...es-208217.html to help you get data structure correct, the all-important first step of development.

[ilikeshinythings]

Ok thanks i'll have a look at them, and i think i grasp what you're saying, still a little confused though. If I use subforms they will all stay linked and display the correcti nformation?

and I think alot of the issues i was having was becuase my ERD was terrible which I have now redone completely, how does this look?

[June7]

A book store would have multiple copies of the same book - an inventory of merchandise. How do you want to handle that?

Be careful about saving credit card info in database.

[ilikeshinythings]

It's only for an assignment in college not for actual use so it doesn't have to be exact or comply with data privacy laws etc. and there are muliple flaws in this ERD such as not being able to order more than one book per order but if you or anyone else could suggest a simple way to implement this i would be grateful.

[June7]

You would need an OrderDetails table. This would be 1-to-many relationship with Orders table.

[ilikeshinythings]

But how would i incorporate more than one book_id or title to it?

[June7]

Each record in OrderDetails would be one book. The foreign keys would be OrderID and BookID. BookID would not be in Orders table.

[orange]

You have a rule that one order must relate to one book. I find that an over constraint unless that is a specific parameter from your course.
In real life a customer could order 1 or several books at one time. And they could have multiple orders.

As June7 mentioned, you will have OrderDetails (LineItems), and it will be related to Books.

In the ERD you posted, was it a package? The inital character in the attributes identifies field/data type etc? It's not part of the proposed field name, right? In your real tables, I recommend you adopt a naming convention that does NOT have spaces in field or object names.

Good luck with your project.

[ilikeshinythings]

Yeah I understand that the one book to one order is a bit stupid but at the time i had no real idea how to add more than one but now thanks to June7 I understand it, thanks.

And yeah that ERD was just rough and I left spaces rather than underscores to make it easier to read for now until I have it finalized. And the characters/symbols at the start tell me whether its a mandatory or optional field or whether it is a primary key or foreign key etc. but yeah i'll amend it all for the database.

Thanks for the help guys, with a solid ERD set out I dont think i will have nearly as many problems as before.

[orange]

No problem.
I agree, you are well on your way. The model /ERD is key to getting a solid foundation.



There is a sample model for a bookstore online.

[ilikeshinythings]

Oh right is there? any ideas of where I could find it?

[orange]

Yes
http://www.databaseanswers.org/data_...tore/index.htm

It's at a conceptual level and has more within its scope than you require, but will help you in reviewing your ERD.

Take a good look at the Business facts requirements also. Key to getting relationships set up.
http://www.databaseanswers.org/data_...tore/facts.htm

Good luck. Work through your own model and make sure you understand why you have what you have.

[ilikeshinythings]

Yeah I'll have a look and see thanks!

EDIT: Wow thats really helped! thanks for that. I'll post back if i run into any more problems which probably will happen haha

[ilikeshinythings]

Ok sorry to repost so early with a problem but I'm still not 100% sure how to create the intersection table between books and orders. I have Order details as the name of the table with Order_ID and Book_ID in it but how does this make it so that I can have a single order with say 5 different books in it ? Or what else do i need to add for this to happen.

Sorry if i'm being stupid but i'm trying to grasp it.