What is the best way to handle this?

[6tr6tr]

I'm not sure what APIs/setup to use for this situation:

A company wants to store data projects they do for clients. Each year, the data fields are set (as a result of gov't requirements) and they won't change for any client project for that year. however, the fields required can (and usually do) change every year. So things they require this year, might not be needed the next year and new fields might be introduced.

While there are likely to be many common fields from year to year, there's no way to guarantee which ones will remain consistent. They also want to be able to do searches on the data and fields, for projects within a year and across years.

What's the best way to handle this? How should the database be handled? Won't it have to constantly create new fields in a table? Or is there another way to handle this?

What's the best way from a "clean architecture" and/or normalization standpoint?

 

[r937]

i have absolutely no problem in a scenario like this to recommend that you create separate tables each year

you said you "want to be able to do searches on the data and fields, for projects within a year and across years"

within a year, no problem

across years, you could search from a subquery which combines selected common columns from target year tables using UNION

but if you're searching within one year on a column that only exists within that year, and you want to combine these results with a search in another year, then obviously the search in the other year cannot be on the same columns, right? so in this case you would need separate selects, with results combined with UNION

helps?


r937.com | rudy.ca

 

[6tr6tr]

right, but separate tables can become a HORRIBLE performance drag. Imagine after 10 years of it creating tables, a search could take a LONG time, especially if a lot of people are hitting it at the same time. That's the real reason I'm not keen on that.

 

[r937]

why would it be HORRIBLE? and why would searching through two of 10 tables (unioned together) be so much worse than searching through one single monolithic table with a WHERE filter on those two years?

r937.com | rudy.ca

 

[carp]

Yet if you try to use a single table, you are going to have to keep adding columns as new ones become required and then change any code that performs DML and/or queries on the table to adjust for the new year's requirements.
While subqueries based on UNIONs may or may not yield the performance you are after, it seems like the more appealing alternative (at least, without knowing more about your situation).

 

[juliane26]

I also agree with r937:

we create internally called 'rolling window tables' or call it whatever you like:
- a table for each year (or month or whatever you need)
- a view for something like current year
- a view for previous year
and so on, just a little naming convention needed.
Nothing stops you to create a UNION ALL view over all tables if you need it. Performance is not worse - but reorgs and stuff are much easier done, also since usually older data gets no updates and doesn't need it.

so which each new year you just have to shift your view definitions ('rolling window'), but no need changing your reports, they are the same accessing the views.

If your table is too big or so, backup issues or deleting old data and stuff will give you much more of a headache.
But if it is smaller size anyway - no worries, do whatever you like.





Juliane