Understanding Matrix Data Storage

(Nic Hubbard) #1

I am working on learning SQL, and I am curious how Matrix stores its data.


I see a ton of tables for assets, such as sq_ast, sq_ast_attr, etc. What I am wondering is, how does Matrix hold different data about the different asset types? Does that sq_ast_attr table hold all the rows of data for ALL different asset types? Or is there some other way that each asset has its attributes stored?



Thanks.

(Greg Sherwood) #2

Matrix uses an Entity Attribute Value (EAV) model for storage. So all attributes values are stored in that sq_ast_attr_val table for all assets. Matrix will join that table, the sq_attr table (to find values by attribute name) and the sq_ast table to grab all the data about an asset.


Try joining those three together using their primary keys and you should be able to select everything where sq_ast_attr_val.assetid = [assetid]

(Nic Hubbard) #3

[quote]
So all attributes values are stored in that sq_ast_attr_val table for all assets. Matrix will join that table, the sq_attr table (to find values by attribute name) and the sq_ast table to grab all the data about an asset.

[/quote]



So this is why there are attribute ids?

(Greg Sherwood) #4

Yep, that is why they are there.

(Rhulse) #5

[quote]
Matrix uses an Entity Attribute Value (EAV) model for storage. [/quote]



Does the Mini (and will MySource 4) use EAV also ?

(Greg Sherwood) #6

It does use EAV although it stores data in tables based on type rather than in one big table. It also uses attribute names + type code for the primary key instead of giving all attributes an ID.

(Anton Babushkin) #7

It's funny you're doing this Nic, I've actually started the exact same thing (mapping out every table + view + function in the MySource DB) as a little side project.