SQL Server Backend Questions.

[JustinS]

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.

 

[pbaldy]

I'll both agree and disagree with your IT people. It's quite easy to add a field (column) to a table in SQL Server. Your app would need to refresh the linked tables to see it, but that's easy as well. That said, your design is not what we call normalized, and their suggested structure sounds like it is. Your proposed structure is probably easier for you to set up, but will be harder to maintain. Each new test will not only require a new field in the table but queries, forms, and reports will need to be modified.

You would normally handle the multiple tests related to an appliance with a subform displaying the appropriate tests. If you wanted to use a crosstab, ChrisO has an example of an editable crosstab here:

ChrisO Samples

Full disclosure, I haven't tried it but his stuff is usually pretty solid.

 

[theDBguy]

Hi. Sounds like you may have a non normalized table structure, which is not good whether you're usin Access or SQL Server. It's not that hard to add new columns in SQL Server, but it doesn't mean it's the way to go.

Edit: Oops, a little slow...

 

[pbaldy]

By the way, for a database person, this is a tipoff to bad design:

The current structure is very straightforward and resembles how something like this might be laid out in excel.

A relational database is rarely structured like an Excel spreadsheet. They simply aren't built to work that way.

 

[JustinS]

I guess I don't understand the concern. I know normalization used to avoid duplicate records, but in this instance there will never be a duplication because every appliance that is tested is a separate entity. It does not matter if another appliance with the same specifications ends up with the same results, it is its own data point and will be analyzed that way.

I have researched normalization and I get why it is used and many of the techniques used in its application. I guess I don't quite understand why my current structure would be considered a bad design. I can see moving the appliance particulars, model #, serial#, efficiency ratings, to a separate table, but to me it makes more sense for the test results to be listed in columnal, if that's even a word, fashion.

Please educate me, I really want to learn.

 

[Galaxiom]

I guess I don't understand the concern. I know normalization used to avoid duplicate records, but in this instance there will never be a duplication because every appliance that is tested is a separate entity.

There is a whole lot more to normalization than avoiding duplicate records.

I guess I don't quite understand why my current structure would be considered a bad design. I can see moving the appliance particulars, model #, serial#, efficiency ratings, to a separate table, but to me it makes more sense for the test results to be listed in columnal, if that's even a word, fashion.

Hard to be sure from what you wrote but I suspect you are probably barking up the wrong tree with the what you imagine needs to go in separate tables.

It doesn't make sense to store test results in columns when you need to routinely add new tests. Any design that requires adding more columns in runtime is not normalised. Your test definitions are data and you are storing that data in the database structure instead of as values. Normalised design adds new features as records to existing existing structures not new objects in the structure.

You should have a TestTypes table with TestTypeID and TestDescription. (There will me more fields too but let's stick to the simplest model for now.) Another table forms a many to many relationship between TestTypes and Products that records which tests are applied to which Products. Another separate table records the test results against individual products

The test are recorded by using a subform. See post #3 of this thread for an example of the way something very similar can be done. Your individual products are like the Students while the Tests are like the Subjects. There is a table that flags entities as Active which is similar to how you would mark a test as applying to a particular ProductID. (It is a long time since I looked at the sample so I can't quite recall the exact details.)

The real art comes when considering how to record results that have different data types (text or numbers) and units but first get your head around the structure of the primary data before tackling that. You should also familiarise yourself with Entity-Attribute-Value data structures. (Google it). That is the way I usually suggest tacking this problem.

 

[theDBguy]

I guess I don't understand the concern. I know normalization used to avoid duplicate records, but in this instance there will never be a duplication because every appliance that is tested is a separate entity. It does not matter if another appliance with the same specifications ends up with the same results, it is its own data point and will be analyzed that way.

I have researched normalization and I get why it is used and many of the techniques used in its application. I guess I don't quite understand why my current structure would be considered a bad design. I can see moving the appliance particulars, model #, serial#, efficiency ratings, to a separate table, but to me it makes more sense for the test results to be listed in columnal, if that's even a word, fashion.

Please educate me, I really want to learn.

Hi. To make sure we give you some information applicable to your situation and help you understand what we mean, can you please post some sample data from your database? I mean, can you please tell/show us what the information in your database looks like.

 

[JustinS]

@theDBguy I have attached a really dumbed-down screenshot of what the final datasheet would need to look like. This structure is not all that different than my current main table.

@Galaxiom Thanks a ton for the information, I will definitely research the topics that you have provided. I have done some testing outside of my current database prior to this post and I laid it out basically like you suggest with one exception. I did not use a "Product Type" table to specify the tests for each type of product. I considered doing that, however, there is potential that additional product types can be added as time goes on. Actually, I think there is a better chance that "Product Type" additions will happen more often than "Test Type" additions.

Not disputing that I need to make structure changes to the tables in my current database, that seems to be a consensus, but it seems like a ton of work to try to avoid the inevitable, backend updates

 

[pbaldy]

Your proposed design is probably easier (for you) to set up, but will be much harder to maintain. What we're proposing will be harder for you to set up initially, but will be virtually maintenance free. Them adding a new appliance/test/whatever shouldn't require design changes. I have what might be a similar situation with vehicle maintenance. We have all kinds of service types. Some vehicle categories (limousines, taxis, buses) need service types that other vehicles don't (analogous to your tests). I have it set up as is being suggested to you here. I don't even know when they add new vehicle categories, service types, etc. With a normalized structure, new vehicle/service types just get added as records in their respective tables and flow through the application as appropriate.

You're not alone in coming from an Excel standpoint and starting off on the wrong foot design-wise; many others have too, me included. The experienced users here are trying to get you back onto the right foot design-wise. The table structure is like a house foundation. Get it right and you'll have a very strong house. Get it wrong and sooner or later it falls down.

 

[pbaldy]

it seems like a ton of work to try to avoid the inevitable, backend updates

Those are almost insignificant, and not the reason for this discussion. It's all the changes to the front end that I'd be worried about.

 

[JustinS]

@pbaldy Thank you for the guidance. I really appreciate your time. I'm definitely going to heed the warnings you guys have presented. With the caveat that I have not been able to completely go through your prior attachment, the biggest frustration in all of this is not being able to pass data through the crosstab query. I am sure that's been said on this site a million times. That one component seems like a gigantic wall sitting in the middle of the entire project.

 

[Galaxiom]

the biggest frustration in all of this is not being able to pass data through the crosstab query.

I wouldn't go down that path. The bound subform with one record added for each completed test is far simpler. Every other technique I have seen involves tons of code and dynamic queries to insert empty records before the form is loaded.

The solution I presented is very simple to use and involves almost no code. Records are written automatically to the table only when they are filled out. Until then they appear in the subform despite not being in the table where the test results are stored.

 

[theDBguy]

@theDBguy I have attached a really dumbed-down screenshot of what the final datasheet would need to look like. This structure is not all that different than my current main table.

@Galaxiom Thanks a ton for the information, I will definitely research the topics that you have provided. I have done some testing outside of my current database prior to this post and I laid it out basically like you suggest with one exception. I did not use a "Product Type" table to specify the tests for each type of product. I considered doing that, however, there is potential that additional product types can be added as time goes on. Actually, I think there is a better chance that "Product Type" additions will happen more often than "Test Type" additions.

Not disputing that I need to make structure changes to the tables in my current database, that seems to be a consensus, but it seems like a ton of work to try to avoid the inevitable, backend updates

Hi Justin. Looking at your table design, it looks more like an Excel spreadsheet than a database table, which I guess many here have already commented about, and I am just repeating. The "tests" shouldn't be the fields in your table, they should be records in a "Tests" table and then become foreign keys to your inspection records table. For example, a properly normalized table (for the first record you're showing in your attached file), might look something like this:

ID	BM	Test	Result
1	BP-245	R I	245,000
2	BP-245	C R	86
3	BP-245	C M	81.2
4	BP-245	STBY Claim	1,299
etc...

Hope that makes sense...

 

[JustinS]

The intent for the project has always been to have the data display in a spreadsheet manner for the end user. The whole goal that for the idea of creating a database was to provide a "container" for test data to be stored, provide a user-friendly data-entry method, reduce the opportunity for incorrect data entry, and present the data in a filterable spreadsheet type format. The picture I posted is what the initiators of the project wanted, within reason my actual sheets are a lot more refined than the example. So how would I get from the normalized format that is being suggested where the test results are records rather than fields without the use of crosstab query? Is there another way to make that data pivot into a spreadsheet form, because that is what the end-users know and like?

 

[cheekybuddha]

Remember, how data is displayed should have no bearing on how it's stored.

 

[zeroaccess]

The intent for the project has always been to have the data display in a spreadsheet manner for the end user. The whole goal that for the idea of creating a database was to provide a "container" for test data to be stored, provide a user-friendly data-entry method, reduce the opportunity for incorrect data entry, and present the data in a filterable spreadsheet type format. The picture I posted is what the initiators of the project wanted, within reason my actual sheets are a lot more refined than the example. So how would I get from the normalized format that is being suggested where the test results are records rather than fields without the use of crosstab query? Is there another way to make that data pivot into a spreadsheet form, because that is what the end-users know and like?

That is handled by queries and reports.

A query can be designed to gather the data you want, sort, group, sum, etc, and output to Excel, where it could then be formatted as desired. Crucial will be collecting and storing it in the right format, as described above. These queries will not work with your current format, ie, records need to be rows, not columns. Think tables like in Excel: look up "tabular data".

A report can be created (again, based on a query) that displays what you want, in the format you want, in a repeatable, one-click fashion as a finished, printable report of each inspection. These two are your ultimate end-goals. I'll echo the above and say that if you take the time to set this up right, you'll love how easily it all works going forward, and you'll learn a lot. I can say this because I've recently taken that same journey in designing my quality inspection database.