I'd be grateful for advice at an early stage in database design to avoid a future big overhaul!
I'm designing a patient management system. I have a table with core patient demographics details and a systemID as the primary key (autonumber) then one-to-many relationships with several other tables. This exceeds the 32 index maximum for Access and so I've re-organised the child tables into groups (e.g. a contacts groups from which stems one-to-many relationships with a GP table, next of kin table, address table etc). The group tables have a one-to-one relationship with the parent, core deompgraphics table, a foreign key ID field (autonumber) and the primary key is the same as the system ID.
This reduces the number of indexes required on the core demographics tables but I've encountered some problems creating subforms where the parent form is created from the core table, a sub-form from group tables and sub-sub forms from the child tables in each group. Access wont automatically create an autonumber ID for the group tables in one-to-one relationships when a new parent record is created, despite enforcing referential integrity, cascading changes/deletes etc. The form I'm creating wont let me enter data into sub-sub-forms without the subform record being created first.
My questions....1) is there a simpler way of reducing the index burden on a single table? 2) If not is their a simple way of automatically creating a record in each group table when a new patient record is added with the same systemID, such that the sub sub forms will accept data in a one-to-many with the group tables?
I'll be migrating the back-end to MS SQL server eventually (which does not have a limit on indexed fields per table) so would like to design it in a way that makes that easy if possible.
Sorry for the long first post!
Doug