Cross Tab Like Entity

Tue, 23 Jun 2009 22:36:29 Z

Hi Im hoping someone can point me in the right direction.

Im looking to create an set of entities that are based on the same table - but in the form of a pivot/crosstab

The following is an example of the sort of thing I am looking to achieve.

We have two tables - eg Objects and Properties

Objects table has two columns -
ObjectID
ObjectName

Properties table has 4 columns including a foreign key to the objects table.
with 3 columns

PropertyID
PropertyName
PropertyType
FK_ObjectID

sample data could be something like

Object
1      Car
2      Motorbike

Properties
1      Make      String      1
2      Colour     String      1
3      Engine     String      1
4      Make       String      2
5      Model      String      2

I would like the entity data model to have 2 entities

Car and Motorbike
Each wth their corresponding fields

e.g. Car
ID Make Colour Engine

I know I can use SQL Pivot to create a view of the data and use stored procedures to handle the CRUD.
But can I create the entities in the designer?

Cheers

John

Cross Tab Like Entity

Thu, 02 Jul 2009 19:03:27 Z

Hi John

You can create an entity from a view and then map those entities to Insert/Update/Delete sprocs for the updating.

THat might be the easiest thing if you can create one view that will spit out cars and another view that spits out motorbikes.

But will there be more vehicle types and therefore more entities eventually?

julie