Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
verronep
Starting Member
15 Posts |
 Posted - 2003-01-02 : 08:20:03
|
| I was wondering if anyone could offer a bit of advice in regards to system design.I've been tasked with creating an inspection system to record field inspections for various groups in the company. Users will be able to create their inspection forms from a list of possible items and data types. System administrators (also users) will be able to add items to the general "pool" and specify their datatypes. (Yes/No radio button, text box, datetime, etc).So obviously the database will need to store the list of items that can be included in an inspection, the things that go to create a specific version of a specific inspection report, and the actual data collected with an instance of a specific inspection report.I'm struggling with the best way to store the collected data in the database. My inclination right now is to store all the raw data in one table, using the sql_variant datatype. Something like so:InspNo | InspType | FieldNo | DefinedDataType | Value------------------------------------------------------15 | 27A | 1 | int | 515 | 27A | 2 | datetime | "12/24/2002 12:20 AM"15 | 27A | 3 | userYesNoRDO | 1In storing the data, Value would be a sql_variant datatype, and I would cast the value as int, varchar, datetime, etc depending on the DefinedDataType before storing it, so I could later check the data type that was stored for doing queries off it.I realize that sql_variant can not store text or image datatypes. I don't anticipate users entering more than two or three sentences in each text entry anyway, much less more than 8000 characters. There probably will need to be images uploaded with a specific inspection, but I don't plan on storing the actual file in the database anyway. (Just the path to the image.)In looking at what I just did, I probably would store the InspType relation to InspNo in a separate table so I don't need to repeat that column for every field in a specific inspection.The front end will be a web application written in .NET with possible future access through a PDA in the field, so I probably will need to worry about some synchronization of data when they dock their units. SQL Server 2000 SP 2 will be the backend.Any thoughts would be greatly appreciated! Thanks.Paul"I have not failed. I have just found 10,000 ways that won't work." - Thomas Alva Edison (1847-1931) |
|
|
|
|
|
|
|
Data Collection System