Page 1 of 5 12345 LastLast
Results 1 to 15 of 73
  1. #1
    ilikeshinythings is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    37

    Book Shop Database Ideas help?

    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

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ilikeshinythings is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    37
    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?



    Click image for larger version. 

Name:	ERD_Cecily_Books.png 
Views:	64 
Size:	25.6 KB 
ID:	6840

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ilikeshinythings is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    37
    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.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    You would need an OrderDetails table. This would be 1-to-many relationship with Orders table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ilikeshinythings is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    37
    But how would i incorporate more than one book_id or title to it?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Each record in OrderDetails would be one book. The foreign keys would be OrderID and BookID. BookID would not be in Orders table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  10. #10
    ilikeshinythings is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    37
    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.

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  12. #12
    ilikeshinythings is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    37
    Oh right is there? any ideas of where I could find it?

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  14. #14
    ilikeshinythings is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    37
    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

  15. #15
    ilikeshinythings is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    37
    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.

Page 1 of 5 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-20-2012, 11:09 PM
  2. Weather Database Ideas
    By bigroo in forum Database Design
    Replies: 4
    Last Post: 01-19-2012, 09:43 AM
  3. Ideas for database creation
    By randolphoralph in forum Access
    Replies: 1
    Last Post: 08-31-2011, 02:08 PM
  4. Any ideas?
    By eripsni in forum Access
    Replies: 9
    Last Post: 08-25-2011, 08:33 AM
  5. Piano Shop Database
    By tomself1 in forum Database Design
    Replies: 1
    Last Post: 02-19-2010, 08:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums