Can you review my simple SQL database and perhaps provide some feedback on things I may have overlooked? (7 Tables)

Question

Here is the SQL script:

create table Employee(
Carnet nvarchar(15) primary key,
Name nvarchar(500),
LastName nvarchar(500)
)

create table Document(
ID nvarchar(20) primary key,
Employee nvarchar(15) foreign key references Employee(Carnet),
Project nvarchar(20) foreign key references Project(ID)
)

create table DocumentsArea(
DocumentID nvarchar(20) foreign key references Document(ID),
AreaID nvarchar(20) foreign key references Area(ID)
)

create table Area(
ID nvarchar(20) primary key,
Name nvarchar(200),
)

create table Project(
ID nvarchar(20) primary key,
Company nvarchar(500) foreign key references Company(Name),
Title nvarchar(500),
)

create table Company(
Name nvarchar(500) primary key,
CEO nvarchar(15) foreign key references Person(Carnet),
NIT nvarchar(40)
)

create table Person(
Carnet nvarchar(15) primary key,
Name nvarchar(100),
LastName nvarchar(100)
)

The database is pretty bare bones except for the bare necessities because I'm going to be told later on what information to save on Documents, etc.

I'm somewhat inexperienced with database designs so maybe I'm forgetting a couple of things. Any suggestions?

Answer 1

Firstly I would suggestion that you use int/bigint IDENTITY columns as your PrimaryKey ID's for each table rather than Name / nvarchar fields.

Answer 2

I would strongly recommend not to make a NVARCHAR(500) your primary key!

create table Company(
Name nvarchar(500) primary key,

Your primary key in SQL Server will automatically be your clustering key, and that should never be anything larger than a GUID (16 byte) - most definitely NOT something that is 1000 bytes large, and quite possibly will change fairly frequently over time.

Unless you already have a valid and small key (like a customer key or such), I'd suggest to use surrogate identifiers instead - you'll save yourself a lot of trouble!

CREATE TABLE dbo.Company(CompanyID INT PRIMARY KEY,
Name nvarchar(500))

Your primary key/clustering key should always be:

• small (max. 16 bytes)
• unique
• stable (never changes)

The trouble with a huge primary key like this is that all the foreign keys also become huge and unwieldy:

create table Project(
ID nvarchar(20) primary key,
Company nvarchar(500) foreign key references Company(Name),
Title nvarchar(500),
)

This will be much easier if you use surrogate ID's:

CREATE TABLE dbo.Project(ProjectID INT PRIMARY KEY,
  CompanyID INT FOREIGN KEY REFERENCES dbo.Company(CompanyID),
  Title nvarchar(500),
)

and all your nonclustered indices on that table will be massively bloated and unmanageable (and not very useful), since the clustering key entry (here: your company name NVARCHAR(500) field) will be added to each and every entry of each and every nonclustered index. You'll be wasting mega- or gigabytes of disk space in no time....

Answer 3

a) You have name and lastname. I would make that firstname and lastname (unless that is how you reference names in your locale).

b) You have a table called Person and one called Employees. If employees are people too, I would group those into one table, and have some other method for dealing with how they differ. But of course that depends on how the data will be used.

c) I don't know what CARNET means, but I would use something more standard, like ID for the ID columns.

d) What everyone else said about using identity cols as PKs in each table.

e) I would have expected some relationship between COMPANY and DOCUMENT, since they are in the database, but I didn't see one.

f) Lots of columns I would add, but since you said that info will come later, I won't comment there.