The decision has been made that the database that I am currently working on will be split on completion, with the backend being migrated to SQL Server. One of the main issues that has been discussed with this proposition is the ability to add new columns to the backend tables.
Here is a basic synopsis of the project and its goals. The purpose of my database is to house appliance testing information so that it can be analyzed to determine what products are performing well and which might need optimization. Currently I have a main table whose primary key is attached to a specific appliance. Each appliance type has its own specific testing requirements, so there is a column for every test type in this main table. Not every appliance is subjected to every test. The current structure is very straightforward and resembles how something like this might be laid out in excel. Data entry, data editing/updating, and data display all work fantastically.
Now the problem. I have been told that it is very difficult to add columns to tables once they have been migrated to SQL Server and, as the backend portion will be administered by our IT team, there have been talks of me restructuring the data tables in a way that would allow for the introduction of new tests which would mean a new column under the current structure. This new structure would create a new record in a "Tests" table for every test performed and tied back to the "Appliance" that would list all of the appliance specific information. One, this would mean completely restructuring many of my tables. I would also have to revamp my data entry forms to use unbound forms because I would need to add multiple test records for every individual appliance record. Finally, I would have to use a crosstab query to flip the test records from rows to columns so that they can be displayed in a datasheet for the end user. As you guys know crosstab queries do not allow you to pass data through them, thus the need for revamped data entry. This really seems to be making a somewhat simple application very complex.
With all of this stated, sorry its so wordy, what advice can you experts share on this topic? Is it really that hard to create a new column in the SQL Server backend? What other methods could be used to allow for expansion? Could I just leave myself several empty pre-formatted columns on the main table so that they can be used when needed? Any insight that you guys can provide will be greatly appreciated.
Here is a basic synopsis of the project and its goals. The purpose of my database is to house appliance testing information so that it can be analyzed to determine what products are performing well and which might need optimization. Currently I have a main table whose primary key is attached to a specific appliance. Each appliance type has its own specific testing requirements, so there is a column for every test type in this main table. Not every appliance is subjected to every test. The current structure is very straightforward and resembles how something like this might be laid out in excel. Data entry, data editing/updating, and data display all work fantastically.
Now the problem. I have been told that it is very difficult to add columns to tables once they have been migrated to SQL Server and, as the backend portion will be administered by our IT team, there have been talks of me restructuring the data tables in a way that would allow for the introduction of new tests which would mean a new column under the current structure. This new structure would create a new record in a "Tests" table for every test performed and tied back to the "Appliance" that would list all of the appliance specific information. One, this would mean completely restructuring many of my tables. I would also have to revamp my data entry forms to use unbound forms because I would need to add multiple test records for every individual appliance record. Finally, I would have to use a crosstab query to flip the test records from rows to columns so that they can be displayed in a datasheet for the end user. As you guys know crosstab queries do not allow you to pass data through them, thus the need for revamped data entry. This really seems to be making a somewhat simple application very complex.
With all of this stated, sorry its so wordy, what advice can you experts share on this topic? Is it really that hard to create a new column in the SQL Server backend? What other methods could be used to allow for expansion? Could I just leave myself several empty pre-formatted columns on the main table so that they can be used when needed? Any insight that you guys can provide will be greatly appreciated.