SQL database views

Question

Hello all,

I have just created a db with 8 tables that each have over 1000000 rows(data collected for every second over the summer :S) I am planning on creating views so that a week can be selected at a time. I was wondering does it go against any unwritten db rules to create several databases that just contain views (one for each week of the summer) or is there a better way of doing this? The reason I would like to create these views is that it would make things easier for the customer of the data.

Answer 1

Sounds like you want to create DATABASES containing views. Sounds like they'd be reading, cross-database, into the source database's tables. That likely would lead to a performance problem down the road. In other words, non-optimal.

Suggest that the views really should belong in the source database. If your strategy is to have a view represent a week, that's perfectly reasonable.

CREATE VIEW InvoicesWeek30 AS
    SELECT foo, bar, CustomerID
    FROM  Invoices
    WHERE InvoiceDate BETWEEN Date1 AND Date2

 ...
 SELECT * FROM InvoicesWeek30 WHERE CustomerID = 99