Help with bookstore db

[D.J.Ida]

Sooo I hope that someone is still reading this thread. I have gone through several forums looking for a solution to a similar problem, and many unsuccessful attempts to solve it, but none have explained why I was having this problem or how to fix it. I realize now my own database is probably not set up correctly, not surprising since I made it by basically applying the same structure I used in Excel to what I'm trying to do in Access.

The extent of my experience with Access is from way back at the university, where we were guided in clicking this and that until we made a very simple database. I am facing a complicated problem. I need to set up a textbook ordering database for my uni bookstore. It needs to list major and minor courses for that academic year and semester, the textbooks in use for those courses, their publishers, and the instructors and assistants who will be using those textbooks. It needs to tell me how many copies of a title we already have in the bookstore (Student copies + Instructor copies - Bookstore copies) and the number of bookstore copies needs to decrease every time a student picks up their textbook. It needs to show the price for the textbooks we buy directly from their publishers, and the offers we get from suppliers when publishers use intermediaries (hence the how-do-I-compare-two-offers-and-display-cheapest FinalPrice). It needs to have a form where I will enter the academic year, year (I, II, etc.), semester and course and have the books details populated (title, author, etc.; how many copies we have, how many we'll need to purchase). It needs to deal with student packages which consist of common courses and three different minors which can be shared between different years (III and IV) or departments (CS, ECON, PS). It needs to generate an order for each publisher and selected supplier, a student packages report for each department and year, as well as for the admin.

How do I do all this with some very basic knowledge? How should the tables be organized? How many of them should I have? How are they connected? I would really appreciate some help with this.

 

[MajP]

I need to set up a textbook ordering database for my uni bookstore. It needs to list major and minor courses for that academic year and semester, the textbooks in use for those courses, their publishers, and the instructors and assistants who will be using those textbooks. It needs to tell me how many copies of a title we already have in the bookstore (Student copies + Instructor copies - Bookstore copies) and the number of bookstore copies needs to decrease every time a student picks up their textbook. It needs to show the price for the textbooks we buy directly from their publishers, and the offers we get from suppliers when publishers use intermediaries (hence the how-do-I-compare-two-offers-and-display-cheapest FinalPrice). It needs to have a form where I will enter the academic year, year (I, II, etc.), semester and course and have the books details populated (title, author, etc.; how many copies we have, how many we'll need to purchase). It needs to deal with student packages which consist of common courses and three different minors which can be shared between different years (III and IV) or departments (CS, ECON, PS). It needs to generate an order for each publisher and selected supplier, a student packages report for each department and year, as well as for the admin.

I laugh every time I see this. "I work in a bookstore and we need to set up a database..." For years it was "I work in a videos store and we need to set up a database ..." or "I work in a record store and ...". But like video, record, and book stores they all went away. I even seen one recently where someone actually said they work in a video store.
In other words this smells strongly like a homework assignment, because I really doubt any University bookstore in the world that needs a Access database for their inventory. That kind of software is pretty turn-key. Sorry It is about as cliché as "I am a student in a small Mid-Western university...".
I apologize if this is not a homework assignment, but I cannot help but laugh.
If it is a homework assignment, then come clean an we can help you piece by piece, but we will not develop a complete solution. Let us see you have some skin in the game. We will teach you how to fish, but you want the fish, the boat, and rod and reel.

 

[D.J.Ida]

I laugh every time I see this. "I work in a bookstore and we need to set up a database..." For years it was "I work in a videos store and we need to set up a database ..." or "I work in a record store and ...". But like video, record, and book stores they all went away. I even seen one recently where someone actually said they work in a video store.
In other words this smells strongly like a homework assignment, because I really doubt any University bookstore in the world that needs a Access database for their inventory. That kind of software is pretty turn-key. Sorry It is about as cliché as "I am a student in a small Mid-Western university...".
I apologize if this is not a homework assignment, but I cannot help but laugh.
If it is a homework assignment, then come clean an we can help you piece by piece, but we will not develop a complete solution. Let us see you have some skin in the game. We will teach you how to fish, but you want the fish, the boat, and rod and reel.

It's not a homework assignment, and I do really need help with this. I am not in the US but in Southeast Europe, and I've been waiting for our IT staff to add a book order module to the management system that's in use, but it's always, we're too busy, we'll do it next month, or next semester, or next year, and I am god damn tired of it, and tired of numerous rows in Excel, and constant worrying that I will leave something out when time comes to send textbook lists to various publishers and suppliers. I tried the Northwind and inventory templates that come with Access, but cannot make heads or tails out of them, so I'd appreciate if you would take me seriously.

 

[MajP]

OK. We can help then. It will require a lot of back and forth. The focus needs to be 100% on defining the requirements in words and then building the proper tables. For now forget about forms and reports. If the tables are correct then everything else is easy. If they are not designed correctly then everything gets hard. It is like framing a house. If the house is not flat, square, and plum it gets tedious. There should be lots of similar examples out there to start with.

 

[D.J.Ida]

OK. We can help then. It will require a lot of back and forth. The focus needs to be 100% on defining the requirements in words and then building the proper tables. For now forget about forms and reports. If the tables are correct then everything else is easy. If they are not designed correctly then everything gets hard. It is like framing a house. If the house is not flat, square, and plum it gets tedious. There should be lots of similar examples out there to start with.

Yes, I gathered that from the previous questions. I'm prepared to work on this if I have someone to help. It's possible that my need for a database will become obsolete next month, when our devs might miraculously turn up and say hey we made this thing for you! Or I might end up using it for the next year; I don't know. But no knowledge is wasted, even if it only useful for no one else but me.

 

[jdraw]

I realize that this thread started in 2004 and as been added to ever since. For the basics of database structure (normalization) that has been referenced, I am going to suggest this tutorial from RogersAccessLibrary. It leads you through a process to work from business requirements to a normalized database. It provides a problem, intermediate steps and a solution. You will learn and what you learn can be used with any database. The secret is to work through the example. (~30 -45 minutes). There are more tutorials from RogersAccessLibrary, as well as many articles from various sites in the Database Planning and Design link in my signature.
Good luck. Please let us know the status as things evolve.

 

[CJ_London]

start by going through the links provided by Micron - post#66 which should answer these questions

How should the tables be organized?,How many of them should I have?,How are they connected?

Once you understand these principles (it's not difficult) you can use excel to start to map things out

create a column called 'field's
think about each process which will define what fields you need
list every item of data you require (might start with your existing excel columns- you can add more as you realise more are required. e.g.

publishername
bookname
price
studentname
datebought
etc

then with your new understanding of normalisation, create a new column, each column will represent a table and a field should appear in only one table. e,g.


fields........................Publishers..............Books.................Students
publishername.....publishername
bookname...............................................bookname
bookprice................................................bookprice
studentname........................................................................studentname
datebought...........................................................................datebought


looking at this you realise it isn't quite right, prices can change over time, so you need a new table and a new field

fields........................Publishers..............Books.................Students...............bookPrice
publishername.....publishername
bookname...............................................bookname
bookprice...............................................................................................................bookprice
studentname........................................................................studentname
datebought...........................................................................datebought

but now you need to link books to publishers and prices to books, so you need a unique identifier (primary key - PK) to link the two

fields........................Publishers..............Books.................Students...............bookPrice
publishername.....publishername
bookname...............................................bookname
bookprice...............................................................................................................bookprice
studentname........................................................................studentname
datebought...........................................................................datebought
PublisherPK...........PublisherPK..........PublisherFK
BookPK....................................................BookPK...................................................bookFK
bookPricePK............................................................................................................bookPricePK
pricedate...................................................................................................................pricedate

note the change of fieldname in the 'child' tables. It is known as the Foreign Key. Because they are in the same row (in the context of mapping out in excel) they provide the links between the tables
Note also that the field names are all unique - many make the mistake of just using 'ID' 'PK', 'Name' etc. Doing it this way, you can see every bit of data is uniquely named in the context of the whole application. Note also no spaces or non alphanumeric characters

Something else you will need to consider when you finally get around to creating your tables - datatypes - so create another column

fields........................datatype
publishername.....text
bookname.............text
price.........................currency
studentname........text
datebought...........date
PublisherPK...........autonumber*
BookPK...................autonumber*
bookPricePK.........autonumber*
pricedate................date

note for the FK, if the PK is autonumber, the FK will be a long

 

[D.J.Ida]

start by going through the links provided by Micron - post#66 which should answer these questions


Once you understand these principles (it's not difficult) you can use excel to start to map things out

create a column called 'field's
think about each process which will define what fields you need
list every item of data you require (might start with your existing excel columns- you can add more as you realise more are required. e.g.

publishername
bookname
price
studentname
datebought
etc

then with your new understanding of normalisation, create a new column, each column will represent a table and a field should appear in only one table. e,g.


fields........................Publishers..............Books.................Students
publishername.....publishername
bookname...............................................bookname
bookprice................................................bookprice
studentname........................................................................studentname
datebought...........................................................................datebought


looking at this you realise it isn't quite right, prices can change over time, so you need a new table and a new field

fields........................Publishers..............Books.................Students...............bookPrice
publishername.....publishername
bookname...............................................bookname
bookprice...............................................................................................................bookprice
studentname........................................................................studentname
datebought...........................................................................datebought

but now you need to link books to publishers and prices to books, so you need a unique identifier (primary key - PK) to link the two

fields........................Publishers..............Books.................Students...............bookPrice
publishername.....publishername
bookname...............................................bookname
bookprice...............................................................................................................bookprice
studentname........................................................................studentname
datebought...........................................................................datebought
PublisherPK...........PublisherPK..........PublisherFK
BookPK....................................................BookPK...................................................bookFK
bookPricePK............................................................................................................bookPricePK
pricedate...................................................................................................................pricedate

note the change of fieldname in the 'child' tables. It is known as the Foreign Key. Because they are in the same row (in the context of mapping out in excel) they provide the links between the tables
Note also that the field names are all unique - many make the mistake of just using 'ID' 'PK', 'Name' etc. Doing it this way, you can see every bit of data is uniquely named in the context of the whole application. Note also no spaces or non alphanumeric characters

Something else you will need to consider when you finally get around to creating your tables - datatypes - so create another column

fields........................datatype
publishername.....text
bookname.............text
price.........................currency
studentname........text
datebought...........date
PublisherPK...........autonumber*
BookPK...................autonumber*
bookPricePK.........autonumber*
pricedate................date

note for the FK, if the PK is autonumber, the FK will be a long

Thank you; my work day is over, so I'll go over this at home tonight. I see it's possible to attach files; would somebody be able to check the actual database as we slowly start to build it? That's probably still a long ways away

 

[CJ_London]

would somebody be able to check the actual database

yes - but start a new thread titled with each specific issue. And we can only check for basic principles unless you provide a detailed summary of what is supposed to happen. Provide the completed spreadsheet as well

 

[pbaldy]

Welcome to AWF. Since Pat closed the older thread, I cut the recent posts out to a new thread so the discussion remained intact.

 

[Pat Hartman]

Thanks Paul, my bridge game was starting so I had to go and forgot to go back.
@D.J.Ida once you get to 10 posts, you will be able to attach objects. Until then we might be able to work with words. In any event, if you are going to post a database, make sure it does not contain any sensitive data. Just run an update query to muddle stuff rather than deleting it because it is important to have data with which to test. Compile the db and fix any compile errors. Compact to reduce the size. Then zip and post the zip file.

 

[D.J.Ida]

Thank you all, I've been reading some of the tutorials I bookmarked last night; this is gonna take some serious work but I'm willing to put in the time and effort
@CJ_London I spent some time last week writing down the things I needed from this database so it's clear what I need to solve. I can talk about that in detail later on. Thanks, @pbaldy that's a good idea.
@Pat Hartman huh I didn't know you could do that, and was just planning to kind of anonymize any data (e.g. just use course codes for courses and initials for instructors). So, note to self - learn how to create a query; compact database is under database tools.

 

[SHANEMAC51]

So, note to self - learn how to create a query; compact database is under database tools.

the main thing in your task is filters by faculty, semester, group, subject, price list, publisher,....

they will help you view the information and it is on the selection that you will issue reports

until you have laid out an example of the database, and the database, even in a draft version, with a minimum of data, is your vision of your task

if you have an excel prototype of a task, it can tell you a lot about the task

 

[D.J.Ida]

the main thing in your task is filters by faculty, semester, group, subject, price list, publisher,....

they will help you view the information and it is on the selection that you will issue reports

until you have laid out an example of the database, and the database, even in a draft version, with a minimum of data, is your vision of your task

if you have an excel prototype of a task, it can tell you a lot about the task

I do have an example, and I'm trying to lay it all out "on paper", so to speak, but it's difficult to present it all in a simple way because there's so many options with some departments having different tracks or pathways, so that the courses offered one year may not be the same next year. Some courses might also need to be moved temporarily from one semester to another.

I basically need a comprehensive list of all courses and the departments they belong to, so that when I select a department, semester and year, I get a detailed listing of courses arranged into student packages for that particular year.
- then I need to send separate price queries to publishers we order from directly, and suppliers for publishers who work only with intermediaries. They don't need to see repeated information for years 3 and 4 which have minor courses in addition to common courses.
- I then need to go into my packages list, enter all offered prices, and have the system recognize and mark the most affordable ones between the several suppliers.
- I need to send the prices to the administration so they can decide which supplier to order from
- finally I need to send firm orders, and keep track of which books have been ordered, delivered or late.

 

[D.J.Ida]

This is the structure of the departments:

Orders for two entities at separate physical locations:

• University
• Secondary school

University Departments:

• CSIS, splitting into CS and IS major from Year 3
  • Minors in years 3 and 4: Electrical Engineering,
  • Economics,
  • Management
• Gaming
• ECON with four pathways:
  1. Econ w/Business, 2. Econ w/Finance, and
  3. Business & Management w/Economics, 4. Business & Management w/Finance
  • Minors in years 3 and 4: Finance
  • Business
  • Management
• PSIR
  • Minors in years 3 and 4: Diplomacy
  • International Law
  • Management
• Medicine
• Dentistry
• Pharmacy

ECON Management minor can be chosen by CSIS and PSIR students as well, students of year 3 and 4 can attend the same minors, or an Econ year 2 course can be a CSIS or PSIR minor in year 3 or 4.

Secondary school:

• Year 1
• Year 2
• Year 3
  • Natural sciences
  • Social sciences
• Year 4
  • Natural sciences
  • Social sciences

 

[D.J.Ida]

Currently using Excel for orders, with the following columns:

Year
Department
Course code & title
Course Instructor
Book title
Author
ISBN
Publisher
Edition
Year
Student copies
Instructor copies
Bookstore copies
E-Resources (Yes / No)
To order (student + instructor – bookstore = total)
Publisher 1 price
Publisher 2 price (these are publishers who accept direct orders)
Supplier 1 price
Supplier 2 price (these are for publishers who accept orders from intermediaries)
PRICE COMPARISON LOCAL SUPPLIERS (this is for management to see the pricing difference between the two suppliers’ offers)
Final supplier (populated using =INDEX(Foreign_suppliers,MATCH(U##,P##:Q##,0)) and =INDEX(Local_suppliers,MATCH(U##,R##:S##,0)) formulas)
Final price (Student package) (populated using a =MAX(P##,Q##) formula for foreign suppliers, and =MIN(P##,Q##) for local suppliers)
Cost for each course, with =SUM formula to calculate the price for that year’s package
Status (Ordered, Delivered, Cancelled, Late, Out of print, Do not order)

 

[D.J.Ida]

The courses offered to students are not static; different pathways and courses are offered based on various factors, and it is possible for a course to be moved from one semester to another when necessary. Not all departments have pathways, and departments have common courses for the first or first and second year before introducing separate specialist courses. Some have MA and PhD programs.

What I need:

I would like to have something where I could select a department, the pathway they’re offering for that year and semester, and generate a list of student packages for each year; using CSIS as an example this is what I have now in Excel:

Year Dep’t Course Instructor Book Author ISBN Publisher Edition Year St. copies. Ins. copies Bookstore Order Pub1 Pub2 Supp1 Supp2 Comparison Final supplier Final price Cost Status

1 CSIS 101 N. Surname Title 1
1 CSIS 102 N. Surname Title 2
1 CSIS 103 N. Surname Title 3
TOTALS ROW

2 CSIS 201 N. Surname Title 1
2 CSIS 202 N. Surname Title 2
2 CSIS 203 N. Surname Title 3
TOTALS ROW

3 CS 301 N. Surname Title 1
3 CS 302 N. Surname Title 2
3 CS 303 N. Surname Title 3
3 minor1 311 N. Surname Title 4
TOTALS ROW

3 CS 301 N. Surname Title 1
3 CS 302 N. Surname Title 2
3 CS 303 N. Surname Title 3
3 minor2 312 N. Surname Title 4
TOTALS ROW

3 IS 301 N. Surname Title 1
3 IS 302 N. Surname Title 2
3 IS 303 N. Surname Title 3
3 minor3 313 N. Surname Title 4
TOTALS ROW

4 CS 401 N. Surname Title 1
4 CS 402 N. Surname Title 2
4 CS 403 N. Surname Title 3
4 minor1 411 N. Surname Title 4
TOTALS ROW

4 CS 401 N. Surname Title 1
4 CS 402 N. Surname Title 2
4 CS 403 N. Surname Title 3
4 minor2 412 N. Surname Title 4
TOTALS ROW

4 IS 401 N. Surname Title 1
4 IS 402 N. Surname Title 2
4 IS 403 N. Surname Title 3
4 minor3 413 N. Surname Title 4
TOTALS ROW

I would need a price query based on the publisher column to be sent to publishers directly or to the suppliers; they wouldn’t have to see the repetitive courses in the list, just Course code and title, Instructor and Assistant so they can provide teaching materials, Book title, Publisher, ISBN, and the number of copies to order.

I would need a report for the admin; they need to see each package but don’t need separate book details; that could be combined into one column, Book details.

It would be great if I didn’t have to enter the information directly into the tables, and I would need to be able to export this information for whenever the developers finish working on other projects. There’s no commercial LMS, it’s all done in house so they’re quite busy, but I can’t wait anymore and continue to use my Excel file, which has grown to several hundred rows.

 

[Pat Hartman]

I wonder if you are making this more complicated than it needs to be. Books are associated with one or more courses. Does it matter to the ordering process if a course is offered in Department A or Department B or which path a student is following? You need to know which courses are offered in the upcoming semester and an estimate of the number of students so you know how many of each book you need to order. You also need to know what you have left over in inventory.

So in the planning for each semester, you need to verify the book(s) required and the version in case there is an update. You need to get confirmation from the teacher if you can sell old versions or have to return/recycle them. Then you can contact your suppliers and get prices for single copies or volume pricing and lead times for fulfillment. Once you have settled on a supplier, for each book, you can place your orders.

You may also need to take into account used books. Some schools will buy back books in good shape from students and resell them the next semester.

You can also maintain a table of students registered for a course in the upcoming semester if you want to make this more student friendly. Then you can make reports of what books you need for each student and calculate the cost for the individual.

I don't know how you organize distribution but that usually takes place sometime during the week before classes start so it is all a big rush. Do you have the time and staff and storage space to try to bundle all the books for each student or will a clerk just pull them from boxes when the student shows up with his list. You having the list of students/courses helps but really isn't necessary. It is just a back up in case the student shows up without his list.

 

[D.J.Ida]

I wonder if you are making this more complicated than it needs to be. Books are associated with one or more courses. Does it matter to the ordering process if a course is offered in Department A or Department B or which path a student is following? You need to know which courses are offered in the upcoming semester and an estimate of the number of students so you know how many of each book you need to order. You also need to know what you have left over in inventory.

So in the planning for each semester, you need to verify the book(s) required and the version in case there is an update. You need to get confirmation from the teacher if you can sell old versions or have to return/recycle them. Then you can contact your suppliers and get prices for single copies or volume pricing and lead times for fulfillment. Once you have settled on a supplier, for each book, you can place your orders.

You may also need to take into account used books. Some schools will buy back books in good shape from students and resell them the next semester.

You can also maintain a table of students registered for a course in the upcoming semester if you want to make this more student friendly. Then you can make reports of what books you need for each student and calculate the cost for the individual.

I don't know how you organize distribution but that usually takes place sometime during the week before classes start so it is all a big rush. Do you have the time and staff and storage space to try to bundle all the books for each student or will a clerk just pull them from boxes when the student shows up with his list. You having the list of students/courses helps but really isn't necessary. It is just a back up in case the student shows up without his list.

I would definitely want to simplify it. As for the used books, the majority of the students keep their books or if they decide they no longer want to keep them, they donate them to the library. Earlier editions aren't returned and the instructor makes accommodations for anyone with the earlier edition. The changes between editions usually aren't significant.

I need this information so I can create a report of all the student packages for the administration and the colleague who will be bundling the books and distributing them to the students. That way if they do turn up without a list of books and say, I'm a 3rd year ECON student with a minor in finance, the colleague can look up the list and see which package to give them. It's easier for them to give the students their bundles instead of having to locate a book in a box somewhere. There's definitely no space for boxes to be in the bookstore, but bundles can be stacked in groups by department and year and given to the students.

The suppliers on the other hand don't need to see all that; for them it's redundant to have a book repeated in several locations in quantities of 5, 7, 15 copies when it can just be listed once as 27 copies. But we do need to list department, course code and title, instructor and the number of students attending the course because that's requested by the publishers in order to provide the instructors with additional materials (if they exist for that book) such as test banks, presentations etc. So a chosen supplier will relay that list to the publisher, they will say ok, we need to upload accompanying materials to this instructor's account.

Yes, I'd like to also include a list of students here. Is there a way to take into account a situation where a student is repeating a course, since in that case they would already have the books and we wouldn't need to order any for them?

These are the tables I've got so far; I hope I understood the normalization tutorial correctly. I'll try to diagram the relationships next.

Institution​

AcademicYear​

SchoolYear​

Semester​

Courses​

Instructors​

Assistants​

TitlesOfAddress​

Departments​

BookTitles​

Students​

Suppliers​

OrderStatus​

SchoolID​

AcademicYearID​

SchoolYearID​

SemesterID​

CourseID​

InstructorID​

AssistantID​

TitleID​

DepartmentID​

BookTitleID​

StudentID​

SuplierID​

StatusID​

SchoolName​

AcademicYearName​

SchoolYearName​

SemesterName​

CourseCode&Name​

InstructorTitle​

AssistantTitle​

TitleName​

DepartmentName​

BookTitle​

StudentLastName​

SupplierType​

StatusName​

SchoolAddress​

InstructorName​

AssistantName​

DepartmentDean​

BookAuthor​

StudentFirstName​

SupplierName​

Postal code​

InstructorDepartment​

AssistantDepartment​

BookPublisher​

SupplierAddress​

City​

InstructorFullTime​

AssistantFullTime​

BookISBN​

SupplierCity​

InstructorPartTime​

AssistantPartTime​

BookEdition​

SupplierCountry​

InstructorE-mail​

AssistantE-mail​

YearPublished​

ContactName​

InstructorPhone​

AssistantPhone​

SupplierPhone​

InstructorMobile​

AssistantMobile​

SupplierFax​

SupplierMobile​

SupplierE-mail​

SupplierWebsite​

Notes​

 

[D.J.Ida]

Wait, students should also have information on which department, year and major/minor they belong to?

Students
StudentID
StudentLastName
StudentFirstName
StudentDepartment
StudentYear
StudentMajor
StudentMinor