CODEWITHSUNDEEP
c#entity-frameworkdatabase-design
field_b
field_b

Reputation: 698

How to solve bad database design with Entity Framework?

I apologize for the strange question; it is hard to put into words. I am forced to work with a database of questionable design and I would like to solve data access issues with the Entity Framework. I am at a loss how to treat this type of design in an object oriented way.

The Item table is the problem. It has fields that may contain different types of data, ranging from Size to Lot Numbers to SO numbers, etc. The name of the field is determined by the ItemDef table, which links to a ItemDefValue table with the actual field names. The tables have been simplified for demonstration purposes.

Create Table Item
(
ItemKey int Primary Key not null,
ItemID1 varchar(100) null,
ItemID2 varchar(100) null,
ItemID3 varchar(100) null,
ItemID4 varchar(100) null,
ItemDefKey int not null --foreign key to ItemDef table
);

Create Table ItemDef
(
ItemDefKey int Primary Key not null,
CustomerKey int not null , -- foreign key to cusotmer table
);

Create Table ItemDefValue
(
FieldCode small not null,
Title varchar(50) not null
ItemDefKey int not null - foreign key to ItemDef table

);

I have solved this problem with DataSets and DataTables by renaming columns based on the ItemDefValue, so I am not looking for a table-based solution. I would like to avoid this type of table-based logic, especially since I am not fond of DataSets and would rather accomplish data access using the Entity Framework.

I would appreciate advise from anyone that has dealt with this kind of problem before. I would specifically like any suggestions on how to treat this kind of database design in an object oriented way, preferably using the Entity Framework.

And if you think there is no other solution than to re-design the database than I will take that advise as well.

Thanks.

Upvotes: 1

Views: 643

Answers (1)

Mashton
Mashton

Reputation: 6415

Messy! A restructure would definitely be best. But, how about creating views that represent the way you'd like the tables to be organised at an object level - and then with EF use those views rather than the tables directly. You'd need to function map the insert/update/delete to stored procedures for dealing with the real tables, but at least from EF side of things you'd be dealing with a decently organised set of entities rather than those tables ...

Upvotes: 3

Related Questions