Designing a database to be used in N-Tier application

Question

I am working on an N-tier application using .NET (C#, SQL Server). I started by designing the database because I believe starting from bottom would a good idea. Now I need your suggestions in building the database to be very flexible. Knowing that the application is intended to built as a group of plug-ins.

The application is a remote control app for many different hardware devices (Cars, Engines, etc) Here is a fragment of the database tables.

1. Devices Table
2. Personnel Table
3. Users Table
4. Roles Table

As you can see the devices table is a table to represent the devices we are controlling and since each each device may have more or less properties than others I want to link each device to its properties which might be in another table in way that is as elegant as possible. The personnel table is for the people responsible for the device for example: Car drivers (there might be more than one driver for each car), Engine operator, etc.

Users are the application users as you can see I have separated the users and roles into two different tables.

What I am looking for is a way to link the devices to their properties. Plus, In the application each device is an instance of an attachable device. Meaning that I can attach a device called say: SIM card to a device called Vehicle. Moreover, are there any design patterns regarding this subject?

Thanks in advance, and please pardon me if I am not clear.

Answer 1

Depending on how you are going to be handling your data layer this database design may be a moot point. If you were to use EntityFramework with a code first approach you can create your logical entities using that and generate the database from your models.

Most ORM based solutions provide some way to create a database from your objects relationship, but although you can quickly and easily get a database up and running which will do EXACTLY what you NEED, if you want to start playing with the schema you may run into troubles, although you can do quite alot of inferring to help it make the database behind the scenes the way you want it.

If this is a greenfield project and you dont need to accommodate any legacy database system I would stop worrying about your database for the moment and just get to writing your logical models and defining their relationships, and let the data storage concerns handle themselves.