0

Is there any Database Server that offers the possibility to do global projection of the entire database? For example suppose that we have 30 tables that have a 'Year' column, and the database has data for the last 5 years, and let's say that we are interested in one year of data at a time, is there any way to do global projection so we can have a view of the database that include only data for one year at a time?

Mark Taylor
  • 1,843
  • 14
  • 17
anouar.bagari
  • 2,084
  • 19
  • 30

2 Answers2

1

A UNION query with a WHERE clause to filter by a year date range should solve what you are describing.

All the major RDBMS support this functionality.

If the tables all have the same schema then it's easy; if not, you will probably have to introduce 'dummy' columns for some portions of the UNION.

[SGBD is the french term for a RDBMS: What does SGBD mean? ]

Community
  • 1
  • 1
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • may be my quesion wasn't clear, what i'm looking for is to have a view of my database, for example my database will include only data for one year at a time so we can query it without any where clause or using UNION – anouar.bagari Feb 05 '12 at 01:36
  • i don't want to make one view that include data of all tables, but samething like a filtred snapshot for the entire database – anouar.bagari Feb 05 '12 at 01:52
  • +1 for the attempt to answer this one. The 'database' will only contain data for one year at a time? even in the question 5 years of data is mentioned... – Tahbaza Feb 05 '12 at 01:59
  • the 'year' constraint was only an example, the problem is more general, it can be applied for others stuations for example a web application that allow people to manage thiere projects or somthing like that..; it will be great if we can store all data in one database and have 'virtual snapshot' according to the id of the client so the application will query the db like if it contains only data for that client – anouar.bagari Feb 05 '12 at 13:30
  • "the 'year' constraint was only an example," - then it wasn't a clear question. – Mitch Wheat Feb 05 '12 at 14:18
1

If you really must not alter existing code to have it only show the past year, then try to make a view for every table, have this view only show you the 'current year' if you want to show anything other than the current year you then can query the source table. You rename the table and name the view as the table was (though this is a generally sloppy practice).

Otherwise you're going to have to use a WHERE clause in all your queries.

Realistically this is something that your ORM should be dealing with NOT your RDBMS.. unless you're doing raw SQL queries in your code (in which case see the start of my answer for the VIEW option).

Seph
  • 8,472
  • 10
  • 63
  • 94
  • the 'year' constraint was only an example, the problem is more general, it can be applied for others stuations for example a web application that allow people to manage thiere projects or somthing like that..; it will be great if we can store all data in one database and have 'virtual snapshot' according to the id of the client so the application will query the db like if it contains only data for that client – anouar.bagari Feb 05 '12 at 13:24
  • The way we manage such things is we restrict access to records at a web-server ORM level – Seph Feb 05 '12 at 17:52
  • We work in .NET so this only applies there.. but depending on the application, for larger projects we use `Entity Framework` or smaller apps where a full ORM isn't required we have started using `Dapper-Dot-Net`, if you don't have an ORM at all and you're effectively using a custom DAL, then I find that `Dapper-Dot-Net` is suitable and can be replaced over your existing DAL with minimal work. – Seph Feb 06 '12 at 05:09