Simplify a database for users

  • Thread starter Thread starter Patsy
  • Start date Start date
P

Patsy

I have a database to track what books are used in what
courses at a college.

I have a table for books, another table for courses, and
a bridge table allows me to assign books to courses.
(I also have a publisher table and another table that
lists all the courses and their sections for a semester)

Referential intregity is enforced on all relationships.

When a book changes, I must:

first delete the record assigning the old book to the
course

Then delete the old book from the textbook table (if no
other course uses it)

then add the new book to the textbook table

and finally assign the new book to the course

whew!

how could I make this easier for users?

I have forms for each of the tables

I prefer not to use cascade update or delete for fear
what the users might do...

please help!

:)

thanks!

then assign the new book to the course
 
Simplest would be to simply add the new book to the books
table (tblBooks). If the books are selected from a combo
box or list box based on tblBooks, the new book will
appear. The old book can stay on the list or not.
The difficulty would come if you are storing the PK from
tblBooks in old course descriptions (or wherever exactly
the user selects the books), deleting a book will delete
it from, say, last year's course description. Teachers
might want to check archives to see when they last used
the book, or if another teacher has used the book, so
maybe you don't want to delete the old books. If not, you
could add a Yes/No field (called Active, or whatever) to
tblBooks. The combo box (if that is how books are
selected) could be based on a query that uses only Active
titles from tblBooks. I'm not sure of the details of your
database, but I would think your needs could all be
handled from tblBooks, or from the form based on the
table.
 
is it possible instead of deleting the record,
the user can tag it as discontinued item?
i mean add a field to effect that matter.

later you can archive those discontinued items
for future report or delete when its not necessary at all
removing those records will be the task of db admin
or will be done during db maintenance/clean up..
 
Patsy said:
I have a database to track what books are used in what
courses at a college.

I have a table for books, another table for courses, and
a bridge table allows me to assign books to courses.
(I also have a publisher table and another table that
lists all the courses and their sections for a semester)

Referential intregity is enforced on all relationships.

When a book changes, I must:

first delete the record assigning the old book to the
course

Then delete the old book from the textbook table (if no
other course uses it)

then add the new book to the textbook table

and finally assign the new book to the course

whew!

how could I make this easier for users?
Click to expand...

Move to an autonumber scheme as the primary key if you are using the LC
number.
Don't delete the record. Change it to reflect the new title and update the
publisher key if needed.
 
Thanks to Bruce, Mike and Mike...

Yes I can forget about deleting the book from the
tbltextbook...but i don't want the old book to be assigned
to the course any longer but do want the new book to be
assigned...so I could have a command button that opens the
frmtextbook and the user could add the new book, then have
a cmd button on that form to open the
frmassignbooktocourse where the user could delete the old
book and then assign the new book to the course...and try
to label the buttons to make it easier for the user to
remember how all this works...they only use the database 3
times a year so the simpler it is the easier it is on me...

:)
 
using the active field type or the other mike would say..
the record source of your list would be all books that are
marked active and hasnt been discontinued...

in that case, for example...you view a previous course,
you can still see that that particular book was used and
discontinued.. and most probably a remark that would say
this book has been replaced by this
 
Back
Top