I'm reasonably new to database design (how many postings here start like that ) and I have two problems that I can't seem to find a solution to.
I am currently using MSAccess, however I will be putting in a MS SQL Server in the Summer as part of another project (not my choice, but a 3rd party product requires it) and so I will probably want to load up the various Access Databases to that at some point.
So my problems:-
I'm trying to write and inventtory package that will also handle my orders for equipment etc. etc.
1) Obviously the information I want to keep on an item depends upon what that item is. (For example a monitor doesn't have any memory and a printer doesn't usually boast a pentium!) So I figured that I could have one table that is the main inventory and then a sub-table for each type of equipment linked to this with a 1-1 relationship (but, and here's the catch, each row in the main table would be linked to ONLY ONE of several sub-tables). What I'm trying to achieve is an object-oriented style database with variable record types.
2) I want to include a notes field in many of my records. This would have to be a sub-table because I want to be able to date-stamp possibly multiple notes on a single record. Rather than set up a seperate sub-table for each master table, is it possible to have a shared notes table and in some way link not just to a record number, but also to a specific (variable) table?
I hope I'm making myself reasonably clear here, but if not, I'm sure someone will ask me to explain myself. I will understand if the answer is something like "Not with Access, but once you have SQLServer then what you do is...." although I'd like to get started now!
So, I throw myself on your collective mercy!