Its a requirement that the user and user profiles have a centralized store. I choose MS Sql server as storage system.
Remembering that simplicity is one of goals I'm trying to achieve I designed a data model very simple. Figure 1 shows the model.
Its also very simple to understand: each application have its own group of profiles. Each user is bound to one or more profiles. The user credentials are stored in tblUser and password filed contains a string already encrypted.
As you can see, I used some hints in table's name:
Tables that contains dynamic data are prefixed with tbl (meaning table). Tables that work as connections between tables are prefixed with rel (meaning relation). I also have a special prefix, ref (meaning reference), to tables that contains static data. In small systems, like this one, it is easy to control the data model and know where the information is stored. But when the system grows it gets much more difficult to remember where we had stored something. That is when the hints make sense.
Some special fields also have a prefix: fk. This means foreign key. And the rest of the name points to the table from where the key come (without table's prefix).
Try to leave some clues about your data model. It's always useful.
Ok. So I will consider the database done. Now I will focus on data access infrastructure.
I decided to use Linq to SQL as data access engine. Why I chose this data access method:
- It is very easy to use and maintain;
- It have a fluid and very friendly interface;
- It generates, automatically, all the mappings between tables and objects.
But this choice also brings me some drawbacks:
- The objects created during the mapping are not POCO (this means they are "fat" with things I don't need or don't want)
- It is slower than a simple data reader. Correcting: it is much slower;
- There is no easy way to isolate the data model. In general, the mapped objects are used as Data Transfer Objects (DTO) between layers and this will bring us to a design highly coupled.
In spite of these drawbacks I will keep with Linq To SQL (L2S) and try to find some work around to those issues. What I am planning:
- To solve the issues 1 and 3 I will use T4 templates and repository pattern to adapt mapped objects and decouple other layers from the data model;
- To address the issue 2 I will use the parallel extensions for Linq. Of course I don't expect to be as fast as the data reader but I hope to see some performance improvements specially in multi core computers.
So lets work:
Create L2S model:
As I mention before it's very easy to create a L2S model. It's just to add a new item to a project and choose LINQ to SQL Classes. I also chose to change the name to UserAndProfileModel.dbml. Picture 2 shows the dialog.
Click Add button and it's done.
The empty model is created. Now is just to drag and drop the tables from the database model and all the mapping objects are created. Figure 3 shows the tables being selected and figure 4 shows the mapping classes over the drawing surface.
After all these steps I consider the data access layer done. The development process from the beginning to here was very simple and easy to understand. I hope to continue this way.
Keep in touch and... happy coding.