Mysql – Why set up static data in views vs. using tables in thesql

best practicesmysql-5.5view

I get an LDAP feed nightly. I get it as a text file and dump/create my LDAPALL table. There are roughly 75K employees times about 50 fields.

I have the following too:

LDAPIMPORTANT – view that stores all 75K but only 15 fields

LDAPSHORT – view that stores all 75k but 5 fields

LDAPAB – view that only stores 9k employees based on two groups (field lookup)

Each of these are used a lot and for different apps and also there are a lot of views written against these views. But there is no updates to them. We do not update employee data. It is just LDAPALL update once a night.

In this circumstance should I create tables from the LDAPALL table instead of views? I could set up jobs to create these tables once a night. What is best practice behind this? Speak in layman's terms because I am a PHP developer made to do all DB admin stuff.

Best Answer

I would simply use the views if they perform. No need to make copies of data unnecessarily, and many database platforms allow indexed or materialized views and filtered views etc.

HOWEVER, MySQL does not support indexed views. So unless the underlying indexes on your table support the various ways you are accessing the data, it might be worthwhile materializing a version of the view yourself.

Related Solutions

Sql-server – Creating a view on a non-existing view

To my knowledge it's not possible to create a view that is missing objects referenced in that view.

Why is it such a pain to know what the dependencies are on your views? You are CREATING them, right?

Do they already exist in another database? If so you can use SSMS or SMO to script them out in dependency order and bypass this issue.

Oracle: How to query a Hierarchical table

FrusteratedWithFormsDesigner has the right direction(+1). Here is what I think you are looking for specifically.

CREATE OR REPLACE VIEW BuildingSubs AS
   SELECT connect_by_root location "Building", location "SubLocation"
   FROM some_table l
   START WITH l.Location IN 
      (
         SELECT location FROM
         (
         SELECT level MyLevel, location FROM some_table 
         START WITH parent IS NULL 
         CONNECT BY PRIOR location=parent
         )
         WHERE MyLevel=3   
      )
   CONNECT BY PRIOR l.location = l.parent;

select * from BuildingSubs; 

Building             SubLocation        
-------------------- --------------------
BLDG-01              BLDG-01              
BLDG-01              Basement             
BLDG-01              Room B-002           
BLDG-01              Floor-01             
BLDG-01              Room 1-001           
BLDG-02              BLDG-02              
BLDG-03              BLDG-03              
Grounds              Grounds

The view accomplishes all three goals. You can query it for a building to find everything it contains and you can query it for a sub-location to find what building it is in.

drop table some_table;
create table some_table (Location Varchar2(20), Parent Varchar2(20));

insert into some_table values ('Org. Name',NULL);
insert into some_table values ('MAINCAMPUS','Org. Name');
insert into some_table values ('BLDG-01','MAINCAMPUS');
insert into some_table values ('BLDG-02','MAINCAMPUS');
insert into some_table values ('BLDG-03','MAINCAMPUS');
insert into some_table values ('Grounds','MAINCAMPUS');
insert into some_table values ('Floor-01','BLDG-01');
insert into some_table values ('Basement','BLDG-01');
insert into some_table values ('Room B-002','Basement');
insert into some_table values ('Room 1-001','Floor-01');

If you don't want to count the building itself as one of the sub-locations you can wrap the existing query in one the eliminates entries in which the building and sublocation are the same.

Best Answer

Related Solutions

Sql-server – Creating a view on a non-existing view

Oracle: How to query a Hierarchical table

Related Question