Suggestions for improvement
Hi,
Newbie here so ignore to your hearts content but...
General naming:
I would name tables in the format "tTableName", queries "qQueryName" etc others use the tbl Suffix - whatever as long as it is consistent. Likewise within each table name the ID field something like CategoryID and directly after it have a string description like CategoryDesc. When you have a few hundred tables to play with it helps! Also the naming "Category is not going to help in the future - what about if a marketing person said we need to group customers into Categories... try tMediaCategory. It also sorts related tables together alphabetically. Lastly within the tables I tend to name each column without spaces... you can make it work but "MediaItemCategory" is bulletproof.
Customers table:
CustomerID (not CustomerSID) should be in the singular, it should be the primary key, it should be the first field and change it to an autonumber instead of text even if you have to recreate all your data.
Categories table:
You seem to have duplicated the CategoryID - what is the purpose of 'Media Item Category'?
Also with lookup lists like category, VHS might not be relevant in the future. Say you have a form that will allow the new media to be inputted and also display the older media. You would probably want a combo box to select the currently available Categories but not the older ones... adding a Hide (Yes/No default No) to your categories table would enable this along with a record source for the combo box along the lines of [select distinct current category for this Media record] UNION [ select * from qCategoryList].
Also a SortOrder can be useful in list type tables. So qCategoryList might end up being SELECT CategoryID, CategoryDesc WHERE Hide = false ORDER BY SortOrder.
Also by embedding the logic for the lookup into a query rather than the user form you can get more intelligent in the future, perhaps a certain supplier only supplies games... the qCategoryList can be recoded to handle this and the forms aren't changed.
Media Table:
This is not fully normailised because the item can only be one of VHS, DVD or GAME. Use CateroryID instead... From table design add a record called CategoryID then make the type LookupWizard... follow the steps. This also creates the linking others have described between the Media table and the Category table.
Rentals table:
I think this should be split into two tables... Rental (or a better name tSale) and Rental item (or tSaleItem). Because: Susan Surandon came in on the 11/05/2006 once but hired multiple items. Other fields that may exist in the tSale table might be DiscountAmt, SaleTotal, Tax, Recieved, RecievedType (CreditCard / Cash) etc. Then each of the items ignore the payment type stuff as a parent record agregates that info.
CategoryID is unnessesary it is implied by the MediaID, just remove it.
Cheers,
Adam