CODEWITHSUNDEEP
c#asp.net-mvcentity-framework
rwkiii
rwkiii

Reputation: 5846

Using EF Code First to key tables/databases on User ID

I need to setup a user preferences table that can be edited in a MVC View by a user. The preferences will be mainly selectable by a dropdown list. There will a couple of dozen preferences. I am using EF code first with MVC5.

As an example. Say there is a master table that contains available preferences.

ShirtColor     Blue
ShirtColor     Red
PantColor      Black
PantColor      Blue
SockColor      White
SockColor      Brown

Then, when a user edits their preferences that will see a series of dropdown allowing them to change their preferences:

Shirt Color:   Blue    <--- Dropdown w/options Blue, Red. Current setting selected.
Pant Color:    Black   <--- Dropdown w/options Black, Blue. Current setting selected.
Sock Color:    White   <--- Dropdown w/options White, Brown. Current setting selected.

The user's preferences will be used as defaults that are applied to product selections in other parts of the site. There is a need to save all user's product selections which could result in a history of 1000's of rows per user.

My concern is database design. Knowing that EF does not handle cross database queries easily, and knowing that these tables will need to be keyed on the user ID, can anybody guide me toward a good solution with respect to the database design using EF code first?

Is there a performance consideration for creating all of the required tables in a single database? Or should I segregate some of these tables across 2 databases? If the latter, how do I key this data by user ID?

The above example scenario greatly simplifies my requirements. The actual data is for Unit of Measure preferences, but it illustrates the idea - a master table of preferences with their definitions that each user can specify in their user profile. My biggest confusion/concern is how I can use EF code first to use the user ID to keep track of this data.

Upvotes: 1

Views: 117

Answers (1)

Yuliam Chandra
Yuliam Chandra

Reputation: 14640

There is a need to save all user's product selections which could result in a history of 1000's of rows per user.

You don't have to store each User's setting in a single row. You might only need two columns. First column is user id, second column is the user setting in a formatted value (could be a json).

User Id   Setting
A         {"ShirtColor": "Blue", "PantColor" : "Black", "SockColor" : "Brown"}
B         {"ShirtColor": "Red", "PantColor" : "Black", "SockColor" : "White"}
C         {"ShirtColor": "Blue", "PantColor" : "Blue", "SockColor" : "White"}

Knowing that EF does not handle cross database queries easily

I'm wondering, which ORM that provide cross database query easily?

Is there a performance consideration for creating all of the required tables in a single database? Or should I segregate some of these tables across 2 databases? If the latter, how do I key this data by user ID?

Above solution I think is sufficient, unless there is really a lot, I mean, A LOT of settings, then you need to find the right column type, either varchar or varbinary (which you will need to save / read the data as byte and you can also compress the data for more compact data).

My biggest confusion/concern is how I can use EF code first to use the user ID to keep track of this data.

Just use a normal Foreign Key concept.

Upvotes: 1

Related Questions