Database Design: Should I use a Materialized View

database-designmaterialized-view

I am designing a database based off of the NBA stats website (http://stats.nba.com/player/#!/201939/?p=stephen-curry)

If you go to the link posted on the player's stat, you will notice that the player's stats are organized based on different factors (such as the season, month, location, home or away, etc), which you can set based on your filter setting.

The question is, how did the person who designed the site database allow for efficient querying of the each players stats. Each player will have a series of game logs, which will record their stats based on each quarter.

Did they use materialized view to calculate the monthly and seasonal stats for efficient querying?

I try to design the database below:

Best Answer

Unless you work on that database, that's not really something we can answer. However, we can speculate.

It could be a materialized view, but it could also be that a job runs on a schedule to calculate the stats. Maybe computed columns. Maybe just really fast hardware with efficient queries and the perfect indexes. So many possibilities.

My bet would be on the job though.

Related Solutions

Design of fact table(s) and dimensions tables for data warehouse

The IBM Redbooks have a couple of good titles on modeling a data warehouse.

You might be better served with an OLTP database in 5NF, though. It's not hard to test simplified versions of both.

Beginner design of biological sampling database

I would rename TreeData to measurements.

Also, unless you really need the Visits table, I wouldn't use it. If designed properly your database would look something like this if you included the visits table:

tree db design

Using the visits table would complicate some of the queries that you're wanting answered. For example, to answer number one you might use something like this in sql server:

select
     t.treeid,
     max(m.height)-min(m.height) as Growth
from trees t
     inner join measurements m
          on t.treeid=m.treeid
     inner join visits v
          on m.visitid=t.visitid
where v.date>=dateadd(y,-2,getdate())
group by t.treeid

without the visits table you could rewrite that like this:

select
     m.treeid,
     max(m.height)-min(m.height) as Growth
from measurements m
where m.date>=dateadd(y,-2,getdate())
group by m.treeid

Best Answer

Related Solutions

Design of fact table(s) and dimensions tables for data warehouse

Beginner design of biological sampling database

Related Question