Are there any problems with creating a long table in the format below where the "Value" column contains multiple data types stored as a varchar(max). I'm trying to avoid infinitely many columns and in this format I can avoid that, especially when we may add new fields. Data would be coming from a structured file where data validation could also be handled.
| id (int) | Carid (varchar(5)) | Field (varchar(25)) | Value (varchar(max)) | Format (varchar(max)) | LastModifiedDate (datetime) |
|---|---|---|---|---|---|
| 1 | ab123 | Make | Toyota | varchar(max) | 1/1/2022 1:00:00 AM |
| 2 | ab123 | Model | Tacoma | varchar(max) | 1/1/2022 1:00:00 AM |
| 3 | ab123 | Year | 2019 | int | 1/1/2022 1:00:00 AM |
| 4 | ab123 | 0-60 Time | 6.6 | decimal(3,1) | 1/1/2022 1:00:00 AM |
| 5 | ab123 | Delivery Date | 5/1/2019 | date | 1/1/2022 1:00:00 AM |
| 6 | ab123 | Comments | blah...... | varchar(max) | 1/1/2022 1:00:00 AM |