Hi,
I’m trying to devise the best structure for a database. I have firstly a list of around 300 location (for a start), each location will be evaluated according to a list of 200 indicators. Each indicator can be Yes or No and can have several attachments and/or hyperlinks, contact and memo field for the same indicator.
I’m trying to devise the best structure for this.
One option, is to have one or several tables (dividing the 200 indicators in around 7 groups), and each indicator will correspond to one field.
This structure raises some problems, as then I can’t devise how to link several attachments and hyperlink or memo field to the same indicator for a specific record.
One other option is to have a table in which each indicator is a record, and has a field to assign location (looking up the location in a location table), a field for Y/N and a nested table within the indicator record to store several attachments or hyperlinks or memo fields.
In this structure, I can’t devise one field that can be a primary key (as each indicator will be repeated as many times as there are locations) – only the pair Location X indicator can be understood as a primary key. Secondly, I'm not sure that the is the best thing to store the indicators as data, as these a fixed values that will not change during evaluation.
One other option I can see, more or less in line with the last mentioned, option is to create one table with the indicators as records (as the option above), for each location, but then the DB would have around 300 tables (for a start), and each time the user wanted to open a new location would have to create a table, already with the indicators as records, but without any other field filled. (if this is the option, how can I create this new tables with record already? is there any VBA command to copy an existing table with a different name?.
I’m not sure if anyone has any other opinion on the best way to devise the structure. I’m particularly concerned with this structural problem, as this database has the potential to be scaled up.
IF anyone has any tip about what would be the best structure, I would be very thankful.
Cheers,
I’m trying to devise the best structure for a database. I have firstly a list of around 300 location (for a start), each location will be evaluated according to a list of 200 indicators. Each indicator can be Yes or No and can have several attachments and/or hyperlinks, contact and memo field for the same indicator.
I’m trying to devise the best structure for this.
One option, is to have one or several tables (dividing the 200 indicators in around 7 groups), and each indicator will correspond to one field.
This structure raises some problems, as then I can’t devise how to link several attachments and hyperlink or memo field to the same indicator for a specific record.
One other option is to have a table in which each indicator is a record, and has a field to assign location (looking up the location in a location table), a field for Y/N and a nested table within the indicator record to store several attachments or hyperlinks or memo fields.
In this structure, I can’t devise one field that can be a primary key (as each indicator will be repeated as many times as there are locations) – only the pair Location X indicator can be understood as a primary key. Secondly, I'm not sure that the is the best thing to store the indicators as data, as these a fixed values that will not change during evaluation.
One other option I can see, more or less in line with the last mentioned, option is to create one table with the indicators as records (as the option above), for each location, but then the DB would have around 300 tables (for a start), and each time the user wanted to open a new location would have to create a table, already with the indicators as records, but without any other field filled. (if this is the option, how can I create this new tables with record already? is there any VBA command to copy an existing table with a different name?.
I’m not sure if anyone has any other opinion on the best way to devise the structure. I’m particularly concerned with this structural problem, as this database has the potential to be scaled up.
IF anyone has any tip about what would be the best structure, I would be very thankful.
Cheers,
