Design objects first, then create the DB

And I am not saying that you should eliminate database planning from your design documents. Not at all. Database design is an important part of the development pocess and should never be overlooked, if required. At the same time, sometimes it's easier to start the design process from another end - the less common one.

From my experience (I should mention that I haven't worked extensively with databases before), designing a database from the very beginning gives it a pretty rigid structure. Once you define the tables, create some stored procedures and link those together, you have a single storage unit and in most cases, when developers start working on their applications, they will try to adapt the application to the existing DB conditions and often they will have to reach specific compromises because a small change to a DB table might ruin an entire chain of connections.

The technique I am talking about here is called ORM, or object-relational mapping. At it's core is the idea that specific user-defined types can be easily converted to values that can be stored in a database (in most cases, SQL-based) - therefore, simple (scalar) value types.

To give an example, if you have an object of type Vehicle that has a set of properties (e.g. weight, speed, color), you cannot directly store it in a SQL database as an object - you have to convert each single property in a simple type and store them in the context of an entity (row) that will represent the Vehicle object.

Starting working on objects first gives you a bit more room and flexibility for testing before you define the database components, since you will be able to mock some data and you won't be tied to a non-flexible structure. 

To give you an example on how this is done in C#, I will create a very basic class that will represent a data model and will use LINQ-to-SQL to build a database around it. And although LINQ-to-SQL is being slowly replaced by Entity Framework, for this short tutorial it will be enough to get you familiar with some of the basic concepts.

So here is the basic class:

public class Vehicle
{
    public string VIN { get; internal set; }
    public string Type { get; set; }
    public string Name { get; set; }
    public DateTime DateOfManufacturing { get; set; }
} 

Obviously, the entity itself cannot be stored in a single cell (I am talking about SQL databases). Here is where you can decorate the class and the properties defined in it with attributes that will define a database component - in this case a table.

Before I go any further, make sure you have the correct references set for:

using System.Data.Linq;
using System.Data.Linq.Mapping;

Once done, here is what I have for the class:

[Table(Name="Vehicles")]
public class Vehicle
{
    [Column(IsPrimaryKey=true,CanBeNull=false,IsDbGenerated=false)]
    public string VIN { get; internal set; }
    [Column]
    public string Type { get; set; }
    [Column]
    public string Name { get; set; }
    [Column]
    public DateTime DateOfManufacturing { get; set; }
}

Here, attributes define the table structure. For example, I am explicitly defining the table name when I am declaring the class. This is not mandatory, but otherwise the table will get the name of the class, and if I will keep it Vehicle, this pretty much means that only one entity is stored there, which is not true.

The same idea applies to columns, however the naming problem isn't here since each property will be a column named after the poperty, therefore I don't have to explicitly set their name.

VIN in my case will be the primary key - it is unique, so it's perfect for a primary key. Also, it is not generated by the DB and cannot be null. If I would be using simple IDs, then there is absolutely no problem in setting the values to be generated by the DB. This will give you a flexible mechanism to get idenitificators to be auto-incremented.

So now there is a structure. How does it become a DB? First of all, the majority of what you'll be working with in LINQ-to-SQL is revolving around DataContext. It is the connecting link between your application and the datbase. 

Given that we already have a basic unit structure, I can create a DataContext instance that connects to my database (with an existing connection string):

DataContext dc = new DataContext("Data Source=SA-PC\\SQLEXPRESS;Initial Catalog=VEHICLE_DB;User Id=user;Password=password;");

NOTE: Do not follow my credentials sample here. It is never a good idea to have it set up like this.

Now that there is a connection, you can pass a table to the DataContext instance:

dc.GetTable<Vehicle>();

This particular generic call will return a set of tables that are based on the passed entity. Since there are no tables defined that would fit the passed generic type, a table will be created according to the rules defined by the attributes.

And at the end, I only have to call this to create the DB:

dc.CreateDatabase();

Even if the database defined in the connection string does not exist, it will still be created. Now, let's take a look at the server and see if the actual database is there:

And sure thing, there it is!

As you can see, using ORM can save you some time (and maybe a couple of lines of code) when it comes to creating a DB based on the specific application requirements rather than going the other way. I would probably hesitate relying solely on an ORM tool to build the skeleton for me when it comes to complex models, since a lot of points will be missed and some particular aspects of the RDBMS will not be used - we all know that there are internal query optimizations that are not always considered by ORM developers.

NOTE: I am talking primarily about building the DB, not obtaining data from it here.