Querying data from multiple clustered tables as one unified

Question

Hello,

I've recently started work on a project which involves creating a web-based reporting interface for a fairly old software responsible for managing some access control hardware like electronic door locks for example.
I have chosen CakePHP for the task and all it involves is querying the database for the log records and displaying them on the website. The software is written in C++ and uses MSDE (a scaled-down version of Microsoft SQL Server 7.0 or 2000) and I was able to configure CakePHP so that it successfully connects to the DB.

The problem is that the developer of this software has chosen to store the logs in the database by creating a separate table for each day. So it looks like this:

tbl_DoorEvent_2008_08_07
tbl_DoorEvent_2008_08_08
tbl_DoorEvent_2008_08_09
tbl_DoorEvent_2008_08_10
...

I am not really familiar with MSDE but I still doubt that it is an acceptable practice to design and maintain a database like this. I do not know how the desktop software manages to parse query all the information when it's all in this format but my CakePHP application is having some really hard time chewing this up.

I want to stick with MVC for the web application but I don't know how to implement the DooreventModel in CakePHP so that it gets the information from all the partitioned tables.
I've tried implementing some hacks like using

UNION ALL SELECT * FROM tbl_DoorEvent_2008_08_07
UNION ALL SELECT * FROM tbl_DoorEvent_2008_08_08
....

and this sort of works but when I want to perform WHERE restriction on the whole query or any other SQL operator it doesn't work.

Also one of the requests was that the web-based application does not add or create any additional databases or tables and only uses the existing ones to display the reports so creating one huge table that includes all of the clustered ones is not really an option.

I just hope that someone will come up with an SQLServer function that will merge all tables into one for me every time I perform a query but I know this is a bit optimistic, so I'm open to all suggestions really. And please remember that the solution must work with CakePHP and as a single Model in the MVC

Answer 1

You probably want a partitioned view?

Answer 2

You can use a where statement if you place the unions in a subquery:

SELECT *
FROM (
    SELECT * FROM tbl_DoorEvent_2008_08_07
    UNION ALL 
    SELECT * FROM tbl_DoorEvent_2008_08_08
) sub
where sub.MyKey = MyValue

Or create a view:

CREATE VIEW vw_MyView
AS
SELECT * FROM tbl_DoorEvent_2008_08_07
UNION ALL 
SELECT * FROM tbl_DoorEvent_2008_08_08

After that you can query the view:

SELECT * from vw_MyView where MyKey = MyValue

P.S. Never use * in production queries, espcially not views.