Unanswered: Multiple tables storing additional data in an shared table?
I'm building an asset register database and have multiple asset tables (PCs, servers, Software, routers, etc) to store details of each item, however they all share some common fields such as supplier, date purchased, warrenty etc, so I thought it would be best to have a single table for those common fields and have each of the other tables linked to it. The relationships are one to one between each of the asset tables to the common fields table.
However how do I ensure that each of the asset tables creates a new record in the common field table and doesn't edit a record that is linked to another record in one of the other asset tables? At the moment each asset table has link its record 1 to the shared fields table record 1, and 2 and so on.
Do I have to use a create new record button on my form that create both a new record in the relevent asset table, and also goes and creates the next available record in the shared field table?
There is a PK in the shared fields table that is linked to a number field in each of the asset tables.