With only four tables: Table, TypeField, Field and Data I can dynamically build & modify any database from my application.
Let's take a very basic example.
Table has 3 fields: PK_Table, Counter, Label
Row examples:
1, 2, Product
2, 1, Order
TypeField has 3 fields: PK_TypeField, Label, Lenght
and cherry on the Cake, Data has only 3 fields: PK_Data, FK_Field, Counter, Value (nvarchar)
Row examples:
1, 1, 1, 1 // first row Product, first PK
2, 2, 1, "chocolate" // first row Product, first Product Label (why chocolate? Because I really love it)
3, 1, 2, 2 // second row Product, second PK
4, 2, 2, "coffee" // second row Product, second Product Label
5, 3, 1, 1 // first row Order, first PK
6, 4, 1, "Order Number 1" // first row Order, first Order Label
7, 5, 1, 2 // first row Order, FK to the Product line 2 which is coffee
That's all, with only 4 tables u can build and change dynamically any Database Model without using SQL Server Mamagement Studio!
It is of course necessary to create relevant Index.
To get all Product Fields?
A very simple select:
SELECT PK_Field, FK_Table, FK_TypeField, Label, FK_Value
FROM Field
WHERE FK_Table = 1
To get all Product rows?
SELECT Data.PK_Data, Data.FK_Field, Data.Counter, Data.Value
FROM Data INNER JOIN
Field ON Data.FK_Field = Field.PK_Field
WHERE (Field.FK_Table = 1)
In a metamodel, a traditional data row is a collection of records, here are the two previous Product records :
I really do not understand why this powerful tool is not available under ASP.Net.
I can easily apply Dynamic Data on the metamodel tables (Table, TypeField, Field and Data) but I would like more..
In particular, to dynamically generate CRUDO views for Table rows..
Scott, do u think it is really difficult to integrate a similar MetaModel within Dynamic Data?
grandjean
0 Points
8 Posts
MetaModel
Jan 24, 2009 08:12 AM|LINK
Hi,
I am implementing a meta model. Do you know it?
With only four tables: Table, TypeField, Field and Data I can dynamically build & modify any database from my application.
Let's take a very basic example.
Table has 3 fields: PK_Table, Counter, Label
Row examples:
1, 2, Product
2, 1, Order
TypeField has 3 fields: PK_TypeField, Label, Lenght
Row examples:
1, PK, 10
2, FK, 10
3, Int, 10
4, Varchar(50), 50
Field has 4 fields: PK_Field, FK_Table, FK_TypeField, Label, FK_Value
Row examples:
1, 1, 1, ProductId, Null
2, 1, 4, ProductLabel, Null
3, 2, 1, OrderId, Null
4, 2, 4, OrderLabel, Null
5, 2, 2, FK_Product, 1 (FK to Product in Order table)
and cherry on the Cake, Data has only 3 fields: PK_Data, FK_Field, Counter, Value (nvarchar)
Row examples:
1, 1, 1, 1 // first row Product, first PK
2, 2, 1, "chocolate" // first row Product, first Product Label (why chocolate? Because I really love it)
3, 1, 2, 2 // second row Product, second PK
4, 2, 2, "coffee" // second row Product, second Product Label
5, 3, 1, 1 // first row Order, first PK
6, 4, 1, "Order Number 1" // first row Order, first Order Label
7, 5, 1, 2 // first row Order, FK to the Product line 2 which is coffee
That's all, with only 4 tables u can build and change dynamically any Database Model without using SQL Server Mamagement Studio!
It is of course necessary to create relevant Index.
To get all Product Fields?
A very simple select:
SELECT PK_Field, FK_Table, FK_TypeField, Label, FK_Value
FROM Field
WHERE FK_Table = 1
To get all Product rows?
SELECT Data.PK_Data, Data.FK_Field, Data.Counter, Data.Value
FROM Data INNER JOIN
Field ON Data.FK_Field = Field.PK_Field
WHERE (Field.FK_Table = 1)
In a metamodel, a traditional data row is a collection of records, here are the two previous Product records :
Data.PK_Data, Data.FK_Field, Data.Counter, Data.Value, Field.FK_Table
1, 1, 1, 1, 1
2, 2, 1, "chocolate", 1
3, 1, 2, 2, 1
4, 2, 2, "coffee", 1
5, 3, 1, 1, 1
I really do not understand why this powerful tool is not available under ASP.Net.
I can easily apply Dynamic Data on the metamodel tables (Table, TypeField, Field and Data) but I would like more..
In particular, to dynamically generate CRUDO views for Table rows..
Scott, do u think it is really difficult to integrate a similar MetaModel within Dynamic Data?
Best regards,
Bruno Grandjean