Help with bookstore db

[MajP]

So lets pretend you have a table of courses
tblCourses tblCourses

CourseID_PK	CourseName
1​	History 101
2​	History 201
3​	Math 101
4​	Math 201
5​	English 101
6​	English 201

And you have a list of books
tblBooks tblBooks

BookID_PK	BookName
1​	Book History 1
2​	Book History 2
3​	Book EFG

Some classes have more than one book, some classes resuse the same book. This is a many to many. A course has many books, and many books can support different courses. There is overlap.
tblBooksCourses tblBooksCourses

bookCourseID	CourseID_FK	BookID_FK
1​	1​	1​
2​	1​	2​
3​	2​	2​
4​	2​	3​

In other words using a query
Query1 Query1

CourseName	BookName
History 101	Book History 1
History 101	Book History 2
History 201	Book History 2
History 201	Book EFG

History 101 uses books History1,2. History 201 continues with History 2 but uses another book EFG.

 

[D.J.Ida]

Based on your responses to me (and in #36 above), YOU are making this application much harder than it needs to be. YOUR application does not need any information about the programs run by the school. All it needs to know is what books are required for what courses for the upcoming semester. And then what students are taking what courses so you know how many books to order and can package them if you have the staff and space to do so. When you want other advice on the app, please start a new thread.

It does need that information, because the admin wants me to present them with a report of every course package within every department, year and semester. I recognize this complicates things, but I don't know how to uncomplicate it for myself and still present the admin with every detail they want to see. I need to have a price comparison for the prices sent by local suppliers and the price difference needs to be visible so that the admin can decide which supplier they will choose.

It's a back and forth process: I collect the information of all course textbooks from the dean of each department, send the price request to suppliers, they send their offers, I prepare them for the admin so the price difference is clearly visible, the admin says alright, we will accept the offer from this supplier. Or they may say, No, they're both too high, let's negotiate. And one of the suppliers may say, we cannot offer a more affordable price, so we will withdraw. Or they may say, Alright since we've worked together for years, here are some discounts, so that needs to be entered into the table and again presented to the admin. Then when everything's been decided, I need to transfer the rows containing books from publishers we order from directly, and those who only work with intermediaries, i.e. one of the suppliers. I worry that during that process I might miss a textbook which will not be ordered on time, and will have to be requested once the semester has already started.

I am trying to simplify this process, but I do not know how. It's been many years since I used the program, and do not have experience with database design; I'm a humanities major. I haven't said that you personally must help me; if your time can be spent more productively helping someone else, thank you again and I will continue to struggle through trying to learn some complicated concepts.

 

[D.J.Ida]

Hi
You said "There's no number one, not the infinity symbol - it's just an ordinary line."

If you double click on the ordinary line and select the option "Enforce Referential Integrity" you will get the 1 to Infinity Symbols

Oh OK, I didn't know that, thank you. I will keep that in mind for the future.

 

[Pat Hartman]

I am trying to simplify this process, but I do not know how.

What you need to do is to recognize what data is required to do your job -- buy books. Even knowing WHICH students need which books is technically beyond your need. You need to know how many students, not which students.

Start with that. If the data regarding the departments and course programs is available elsewhere, let them make the report. You should NOT be duplicating all that data. They should be giving YOU this report not asking you to recreate the wheel and manage data that has nothing to do with buying books. Do YOUR job, not theirs. The intersection between the other applications and yours is the list of courses for the next semester and which books each course requires. That is what they give to you. You create RFQ's, order the books once a vendor is selected. Stock the books, distribute them to students based on what classes the student has signed up for. The other data already exists in some other application. Use it if you need to but do not duplicate it.

Learn how to separate nice to have from necessary. If you focus on your job, even with little experience, you will have something within a couple of weeks that you can use. One of us could do the core application in probably a week. It really isn't very difficult until you complicate it with unnecessary data collection and management.

 

[D.J.Ida]

What you need to do is to recognize what data is required to do your job -- buy books. Even knowing WHICH students need which books is technically beyond your need. You need to know how many students, not which students.

Start with that. If the data regarding the departments and course programs is available elsewhere, let them make the report. You should NOT be duplicating all that data. They should be giving YOU this report not asking you to recreate the wheel and manage data that has nothing to do with buying books. Do YOUR job, not theirs. The intersection between the other applications and yours is the list of courses for the next semester and which books each course requires. That is what they give to you. You create RFQ's, order the books once a vendor is selected. Stock the books, distribute them to students based on what classes the student has signed up for. The other data already exists in some other application. Use it if you need to but do not duplicate it.

Learn how to separate nice to have from necessary. If you focus on your job, even with little experience, you will have something within a couple of weeks that you can use. One of us could do the core application in probably a week. It really isn't very difficult until you complicate it with unnecessary data collection and management.

"They should be giving YOU this report--" that would be nice, but it's not the reality. Is there a way to make this work within the constraints of what I have to do?

 

[D.J.Ida]

It is nearly midnight, and I will be retiring, but could someone tell me if I'm on the right track when it comes to organizing my tables or entities, if that's what they're called? I haven't yet attempted to create any relationships before I determine if I'm missing some information, or if some of it is redundant. There should be a quotes and orders table, where I could input the prices, but I wasn't certain if these should go together or be separate, or in the suppliers table.

Academic year is 2022/23, school year is Year I, II etc. Titles of address are Assistant, Assistant Professor, Professor, Docent etc. Supplier type is local or foreign. Status ordered, arrived, late, out of print etc.

 

[Pat Hartman]

No, you are not on the right track. You really, really, really need to concentrate on YOUR job. Three of the fourteen tables are relevant to ordering books. I'm done.

 

[D.J.Ida]

No, you are not on the right track. You really, really, really need to concentrate on YOUR job. Three of the fourteen tables are relevant to ordering books. I'm done.

Again I thank you for your time, and repeat that the reality is that I also have to do these other things. Book orders are just a small part of my job.

Can someone tell me which tables they are (I'm guessing books, courses and suppliers) and how to integrate the less relevant tables (which I nevertheless have to take into account because that's what the management wants to see) and how to make the necessary forms and queries for the multi-step process: 1. collect information from deans, 2. send price queries to publishers and suppliers, 3. enter prices as they come in, possibly change them if there are negotiations to lower them, 4. generate a detailed report for the admin with all the student packages and their prices (e.g. year 3 CSIS package with EE costs this much, with ECON this much, etc. etc.), 5. generate firm orders for publishers and suppliers, 6. keep track of the status of three separate orders (direct-order publishers 1 and 2, and the chosen local supplier for other publishers, so I need to associate those publishers with the suppliers we work with).

Thank you in advance.

 

[Pat Hartman]

I also have to do these other things

They are NOT your priority. Leave that to the IT people. Do what YOU need to do YOUR JOB. You can actually accomplish YOUR needs in a week or two if you are a good student. That job appears to be ordering the correct books in the correct quantities from the most favorable quote. Secondarily, you need to manage inventory. Thirdly, you need to distribute to students. This can be simple, they walk in and tell you what they need or what courses they are taking and you figure out what they need OR you somehow assemble all the books for each student together. This is nice but not necessary. It will be a royal pain if you don't have every book you need when you start the assembly process. You will have to keep going back and finding the right student pile, breaking it open, and adding a book at a time to it. Each time, wrapping the package up again. This is a serious waste of time and will introduce errors into the process. If you insist on doing the job of the Curriculum department, you will be at this for a year. Just let them give you the data and as long as the data includes the list of books for each program, all you need to do is to assign final prices to them. OR, you give them the final price list by book and let them make their own report.

When you waste time and money on duplicating and managing data kept by other departments, you are taking value from the students. That time and money could be better spent on reducing tuition or offering other services.

 

[D.J.Ida]

They are NOT your priority. Leave that to the IT people. Do what YOU need to do YOUR JOB. You can actually accomplish YOUR needs in a week or two if you are a good student. That job appears to be ordering the correct books in the correct quantities from the most favorable quote. Secondarily, you need to manage inventory. Thirdly, you need to distribute to students. This can be simple, they walk in and tell you what they need or what courses they are taking and you figure out what they need OR you somehow assemble all the books for each student together. This is nice but not necessary. It will be a royal pain if you don't have every book you need when you start the assembly process. You will have to keep going back and finding the right student pile, breaking it open, and adding a book at a time to it. Each time, wrapping the package up again. This is a serious waste of time and will introduce errors into the process. If you insist on doing the job of the Curriculum department, you will be at this for a year. Just let them give you the data and as long as the data includes the list of books for each program, all you need to do is to assign final prices to them. OR, you give them the final price list by book and let them make their own report.

When you waste time and money on duplicating and managing data kept by other departments, you are taking value from the students. That time and money could be better spent on reducing tuition or offering other services.

Thank you for your advice, but we obviously cannot see eye to eye. I keep telling you what our current process is, as directed by the admin, and you keep telling me what I should change. That is not an option at this moment. We DO assemble all books for each student. Everything is ordered a few months in advance. The shipments get unpacked, and the books for a particular year are stacked together for each student, so when they come in and say, I'm student of this department and year, my colleague looks up the list of courses and books for that department, semester and year and gives them the package. They sign for it, take it and leave.

The IT department already has projects underway, and are already outsourcing some of the work because they cannot do it themselves. I on the other hand cannot wait for them to create a module that will connect to other parts of the in-house system to pick up the number of students and the information on whether there's been any change of courses since the last semester or if they're using the same books. I came to ask for help on how to do this particular task under these particular conditions. I understand you believe the process should be adjusted. Again, this is not an option. I would like to get this database working, as best as possible, by the time the fall semester starts. Again, respectfully, if you don't see how your advice can help with this task, perhaps someone else would benefit from your help. Thank you for the effort you put in so far.

 

[Pat Hartman]

You are correct. I cannot help you. I was trying to get you to concentrate on the heart of the problem first. Good luck with your project.

 

[D.J.Ida]

Also, has it occurred to you that I would like to learn this program for myself? I am an amateur bookbinder, and lately people have been contacting me to bind blank and printed works for them. I thought it would be interesting to try and make a database in which I could track these requests and the resulting items. It's also another skill I could add to my CV; I'm proficient in Word, fairly adept in Excel and PowerPoint, but know nothing of this particular Microsoft product. If I have to attend classes live, I will, but I had hoped that someone would have an idea about how to try and solve this issue by the time our next semester comes.

 

[D.J.Ida]

You are correct. I cannot help you. I was trying to get you to concentrate on the heart of the problem first. Good luck with your project.

Thank you. Believe me, I would like to just focus on the specific things you mentioned, but I cannot. All of these other things are also my duties.

 

[Pat Hartman]

I would love to change the world. There is very much which needs fixing but if I can't focus on something small and close to home, I can never accomplish anything. I am trying to convince you to focus. I've been developing software for over 50 years. I understand how it works and what is important. Do not try to solve the world's problems. Solve your problem and perhaps work out if you can. Everything you do that goes toward solving your specific problem gives you a foundation on which to build so that you can solve other department's problems later. It isn't even your job to create software. You are making software creation your job because you have an immediate need. Solve your problem first. You don't even know how to program. Set yourself goals that you can achieve.