This is question isn't a specific problem but an index design query.
I have developed an Admin database that produces project info, quotes on the project, itemises them assigns manpower and cost codes and links costcodes to a PO table this is relational through several one to many relationships and a couple of Many to many relationships.
I have also designed a database template for our site engineers which will allow them to enter out of pocket expenses and track all hours related to the Project. There may be 5 or more seperate project db's outstation at any one point.
All data is returned to the main Admin database by a series of VBA modules after the on site database tables are placed on a ftp server.
My concern is the best way of maintaining the integrity of the data in the Admin database. Obviously use of Autonumber fields is out.
If I use code to set the table PK indexes it means that I have to then ensure that this is copied back to the onsite project databases. My thought is to ensure uniqueness by using the ProjectID as the primary key through all the tables with a unique identifier on each table. By the time this reaches the last table of the one to many relationships the PK is made up of 4 fields on the table holding the most data.
Is this a common approach and most importantly will all this indexing inhibit the database performance? Is there a better method to use to achieve data validation? Another method I thought of was Assigning PK ranges through code to each outstation db created.
Thanks for you help
Regards
Jon Simpson
I have developed an Admin database that produces project info, quotes on the project, itemises them assigns manpower and cost codes and links costcodes to a PO table this is relational through several one to many relationships and a couple of Many to many relationships.
I have also designed a database template for our site engineers which will allow them to enter out of pocket expenses and track all hours related to the Project. There may be 5 or more seperate project db's outstation at any one point.
All data is returned to the main Admin database by a series of VBA modules after the on site database tables are placed on a ftp server.
My concern is the best way of maintaining the integrity of the data in the Admin database. Obviously use of Autonumber fields is out.
If I use code to set the table PK indexes it means that I have to then ensure that this is copied back to the onsite project databases. My thought is to ensure uniqueness by using the ProjectID as the primary key through all the tables with a unique identifier on each table. By the time this reaches the last table of the one to many relationships the PK is made up of 4 fields on the table holding the most data.
Is this a common approach and most importantly will all this indexing inhibit the database performance? Is there a better method to use to achieve data validation? Another method I thought of was Assigning PK ranges through code to each outstation db created.
Thanks for you help
Regards
Jon Simpson
Last edited:
