I am attempting to create some table relationships for a SQL backend database (using Alpha Anywhere for the frontend) that handles service activity on a work order ticket. I have one-to-many relationships for these tables and have a question.
I have WOT (Work Order Ticket) table for the creation of a work order ticket.
The WOT can have multiple service activities on different dates associated with it.
Each service activity can have multiple parts used (for a repair).
This is just the basic idea, but I have created the following tables and relationships:
WOT Table:
wot>wotnum -PK
Service Activity Table:
service_activity>serviceid – PK
service_activity>wotnum – FK (link to PK in WOT table)
Part Used Table:
partused>partusedid – PK
partused>serviceid – FK (link to PK in Service Activity table)
Each of the tables above has other columns as well (not shown), but they are unique to the table, such as date fields, part numbers, etc.
The service_activity>serviceid (PK) field is an autoincrement field and so is the partused>partusedid (PK) field as well.
My question is, during data entry, how do I insure the partused>serviceid field (FK) s synced with the service_activity>serviceid field (PK) without actually having to manually enter the partused>serviceid field (FK)?
Although I have a decent understanding of tables and relationships (critical to get this correct now), I am a bit of a neophyte as to the process of thinking through how the tables will interact during actual data input. I think the answer to this may be simple, but I am just not grasping it yet. If my current solution does not seem adequate, I would welcome a suggestion. I need some help to get going in the right direction.
I have WOT (Work Order Ticket) table for the creation of a work order ticket.
The WOT can have multiple service activities on different dates associated with it.
Each service activity can have multiple parts used (for a repair).
This is just the basic idea, but I have created the following tables and relationships:
WOT Table:
wot>wotnum -PK
Service Activity Table:
service_activity>serviceid – PK
service_activity>wotnum – FK (link to PK in WOT table)
Part Used Table:
partused>partusedid – PK
partused>serviceid – FK (link to PK in Service Activity table)
Each of the tables above has other columns as well (not shown), but they are unique to the table, such as date fields, part numbers, etc.
The service_activity>serviceid (PK) field is an autoincrement field and so is the partused>partusedid (PK) field as well.
My question is, during data entry, how do I insure the partused>serviceid field (FK) s synced with the service_activity>serviceid field (PK) without actually having to manually enter the partused>serviceid field (FK)?
Although I have a decent understanding of tables and relationships (critical to get this correct now), I am a bit of a neophyte as to the process of thinking through how the tables will interact during actual data input. I think the answer to this may be simple, but I am just not grasping it yet. If my current solution does not seem adequate, I would welcome a suggestion. I need some help to get going in the right direction.
Comment