Looking at each of the tables, there are a few design problems that stand out.
Depending on the target countries and language, some country names could be longer than 50 characters. For example, "Separate Customs Territory of Taiwan, Penghu, Kinmen, and Matsu (Chinese Taipei)", the longest English country name, is 80 characters long, meaning you would get an error from the SQL database in
[Name] [nvarchar](50) if you tried to insert this value.
[Code] [nchar](10) there is already some official lists of country codes (reference) which could only be a
[nchar](2) field for ISO codes, or a
[nchar](3) field for UN codes. Using a standard country code would also make it more standards-compliant.
[Message] [nvarchar](max) and
[Stacktrace] [nvarchar](max) are problematic. You should avoid using the
MAX types unless you actually need to store up to 2 GB of text in a field (or just over 1 billion 2-byte characters - source). See Using MAX text or more specific, smaller type from Database Administrators site. The reasoning is in large part because the
MAX types are stored outside of the table, only a reference to the field is stored in the table, and as such they are optimized differently.
[CreatedDate] [nvarchar](max) should instead be a
[datetime2] or similar type like you are using in other places, and not a character field. Never store dates and/or times in a text field (and especially not in a
MAX text field), as it makes it much more difficult to manipulate the data.
The only exception to this is if you are storing/receiving data from a system where date/time values are stored in a non-standard text format like
Wed 06-Jul-16 10:31 AM but even in that case it would be better to try to convert it to a compatible format before storing it in the database as a date/time field.
This table contains mostly IDs from what looks to be other tables, however I don't see any foreign key references to the other tables, which defeats the primary purpose of have keys for rows in the first place. Here is some useful information on MSDN.
Also make sure to set a
PRIMARY KEY constraint on the main ID field of each table. The syntax for keys is like this:
CREATE TABLE [dbo].[Postcard](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Postcard] PRIMARY KEY NONCLUSTERED ([Id]),
[CountryFromId] [int] NOT NULL,
CONSTRAINT [FK_Postcard_CountryFromId] FOREIGN KEY ([[CountryFromId])
[CountryToId] [int] NOT NULL,
CONSTRAINT [FK_Postcard_CountryToId] FOREIGN KEY ([[CountryToId])
Using an 8-byte
[bigint] type for your
Postcard.Id seems like an overly large type. According to MSDN the range for
[bigint] is "-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)". If you really plan on having that many postcard records then fine, but otherwise just an
[int] type would be OK.
For consistency your
[Status] [int] should probably be instead named