0

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
learning2cod3
  • 13
  • 3
  • Looks a bit like [EAV](https://en.wikipedia.org/wiki/Entity–attribute–value_model) with a couple of extra columns tacked on. In *most* circumstances, it's a bad idea because you can't use any of the normal relational tools to optimize queries against it (e.g. no indexes, no foreign keys, no check constraints, etc) – Damien_The_Unbeliever Mar 09 '23 at 15:25
  • And e.g. try to sit down and write an actual check constraint you could apply to this table that would actually ensure the values in the `Value` column conform to what the `Format` column says. So you just have to trust that incoming data is correct rather than being able to validate any of it. (And please, if you are going to store dates as strings, use ISO8601 formats rather than an ambiguous one where I don't know if row with ID 5 is about something in May or January) – Damien_The_Unbeliever Mar 09 '23 at 15:27
  • Composite values store multiple values into a single column. Examples are arrays, list of values in VARCHAR, JSON docs, XML docs, CLOBs, etc. It's only a good a idea to do this if you are going to be reading, writing, and searching them **as a whole**. If at any point in time you consider "searching or updating parts of it", then that would become a performance bottleneck, not to mention data integrity issues. – The Impaler Mar 09 '23 at 15:35
  • @Damien_The_Unbeliever Thanks! I was looking for something like the EAV link you included, but didn't know how to word it when searching. I'm not too worried about validation as that will primarily be taken care of in an excel model where wrong values would "break" the model and be transparent. I could also validate when uploading from excel doing something like "cast(Field as Format)" and throwing an error if it does not comply. I will read through the EAV documentation to see better see if it will work in my specific example. – learning2cod3 Mar 09 '23 at 15:50
  • @TheImpaler. Makes sense. I would be uploading/pulling all rows for a record set (carid, lastmodifieddate) at once vs just pulling/updating the "Make" field. My example was simplified to keep it high level. The values are multiple inputs/outputs from a model, so I want to capture everything the model is run and the number inputs/outputs may grow/shrink over time. – learning2cod3 Mar 09 '23 at 15:57

0 Answers0