Solid Schema or NOT?!? - Microsoft SQL / MS SQL Server

[Alex]

Can someone tell me if this schema design is good or what
I need to change in your opinion. I will respond back to
any replies, so please check. Thanks everyone.

CREATE TABLE [dbo].[Storages] (
[StorageID] [int] IDENTITY (100, 1) NOT NULL
PRIMARY KEY,
[EmpID] [int] NOT NULL ,
[StorageName] [varchar] (50) NOT NULL ,
[StreetAddress] [varchar] (150) NOT NULL ,
[City] [varchar] (50) NOT NULL ,
[State] [char] (2) NOT NULL CHECK ([State]
like '[A-Z][A-Z]'),
[PostalCode] [char] (5) NOT NULL CHECK
([PostalCode] like '[0-9][0-9][0-9][0-9][0-9]'),
[Country] [varchar] (25) NOT NULL DEFAULT 'USA',
[Phone] [char] (12) NULL CHECK ([Phone] like '[0-9]
[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]') ,
[Priority] [char] (1) NOT NULL CHECK ([Priority]
like 'P' or [Priority] like 'S'),
[Comments] [varchar] (350) NULL ,
[DateCreated] [datetime] NULL DEFAULT (getdate()),
[UserName] [varchar] (25) NULL DEFAULT (suser_sname
())
)
GO

CREATE TABLE [dbo].[Addresses] (
[AddressID] [int] IDENTITY (100, 1) NOT NULL
PRIMARY KEY,
[EmpID] [int] NOT NULL ,
[AddressType] [char] (1) NOT NULL CHECK
([AddressType] like 'P' or [AddressType] like 'S'),
[StreetAddress] [varchar] (150) NOT NULL ,
[City] [varchar] (50) NOT NULL ,
[State] [char] (2) NOT NULL CHECK ([State]
like '[A-Z][A-Z]'),
[PostalCode] [char] (5) NOT NULL CHECK
([PostalCode] like '[0-9][0-9][0-9][0-9][0-9]'),
[Country] [varchar] (25) NOT NULL DEFAULT 'USA',
[DateCreated] [datetime] NULL DEFAULT (getdate()),
[UserName] [varchar] (25) NULL DEFAULT (suser_sname
())
)
GO

CREATE TABLE [Employees] (
[EmpID] [int] NOT NULL PRIMARY KEY,
[TeamID] [int] NULL ,
[LarkID] [varchar] (10) NULL ,
[AcctCustCode] [char] (6) NULL CHECK
([AcctCustCode] like '[0-9][A-Z][A-Z][A-Z][A-Z][A-Z][A-
Z]'),
[BaseCity] [varchar] (50) NULL ,
[Region] [varchar] (25) NULL ,
[TerritoryCode] [char] (6) NULL CHECK
([TerritoryCode] like '[A-Z][A-Z][0-9][0-9][0-9][0-9]'),
[TerritoryCode1] [char] (6) NULL CHECK
([TerritoryCode1] like '[A-Z][A-Z][0-9][0-9][0-9][0-9]'),
[FirstName] [varchar] (50) NOT NULL ,
[LastName] [varchar] (50) NOT NULL ,
[DateEffective] [datetime] NULL ,
[DateHired] [datetime] NOT NULL ,
[PositionType] [char] (4) NOT NULL CHECK
(PositionType like 'FULL' OR PositionType like 'FLEX'),
[Title] [varchar] (25) NOT NULL,
[DepartmentID] [int] NOT NULL,
[Home] [char] (12) NULL CHECK ([Home] like '[0-9]
[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]') ,
[HomeOffice] [char] (12) NULL CHECK ([HomeOffice]
like '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-
9]') ,
[Work] [char] (12) NULL CHECK ([Work] like '[0-9]
[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]') ,
[Fax] [char] (12) NULL CHECK ([Fax] like '[0-9][0-
9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]') ,
[Cell] [char] (12) NULL CHECK ([Cell] like '[0-9]
[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]') ,
[Email] [varchar] (50) NULL,
[Extension] [smallint] NOT NULL ,
[NTLogon] [varchar] (35) NOT NULL,
[Photo] [varchar] (50) NULL ,
[ReportsTo] [varchar] (100) NOT NULL ,
[IsActive] [char] (1) NULL CHECK ([IsActive]
like 'Y' or [IsActive] like 'N'),
[EmpTypeID] [int] NOT NULL ,
[Notes] [varchar] (350) NULL ,
[DateCreated] [datetime] NULL DEFAULT (getdate()),
[UserName] [varchar] (25) NULL DEFAULT (suser_sname
())
)
GO

CREATE TABLE [dbo].[TeamTypes] (
[TeamID] [int] NOT NULL PRIMARY KEY,
[TeamType] [varchar] (25) NOT NULL,
[DateCreated] [datetime] NULL DEFAULT (getdate()),
[UserName] [varchar] (25) NULL DEFAULT (suser_sname
())
)
GO

CREATE TABLE [dbo].[EmpTypes] (
[EmpTypeID] [int] NOT NULL PRIMARY KEY,
[EmpType] [varchar] (25) NOT NULL,
[DateCreated] [datetime] NULL DEFAULT (getdate()),
[UserName] [varchar] (25) NULL DEFAULT (suser_sname
())

)
GO

CREATE TABLE [dbo].[Departments] (
[DepartmentID] [int] NOT NULL PRIMARY KEY,
[DepartmentName] [varchar] (40) NOT NULL
)
GO

ALTER TABLE [dbo].[Addresses] ADD
FOREIGN KEY
(
[EmpID]
) REFERENCES [dbo].[Employees] (
[EmpID]
)
GO

ALTER TABLE [dbo].[Storages] ADD
FOREIGN KEY
(
[EmpID]
) REFERENCES [dbo].[Employees] (
[EmpID]
)
GO

ALTER TABLE [dbo].[Employees] ADD
FOREIGN KEY
(
[TeamID]
) REFERENCES [dbo].[TeamTypes] (
[TeamID]
)
GO

ALTER TABLE [dbo].[Employees] ADD
FOREIGN KEY
(
[DepartmentID]
) REFERENCES [dbo].[Departments] (
[DepartmentID]
)
GO

ALTER TABLE [dbo].[Employees] ADD
FOREIGN KEY
(
[EmpTypeID]
) REFERENCES [dbo].[EmpTypes] (
[EmpTypeID]
)

[Anith]

Alex,

Generally, without a thorough understanding of your business model, one
cannot comment on the correctness of a schema. However, general suggestions
are possible based on the information you provide. Could you briefly explain
what this DDLs represent? What are the entities in your model? How are they
related ? What are the attributes for these entities? What are the
dependencies among them? The answers to these questions help understand if
your logical schema design is correct or not.

--
- Anith
( Please reply to newsgroups only )

[Anith]

Actually, proper design involves accurately yzing the business model,
applying relational principles on the model and implementing the model using
a DBMS. This requires, as mentioned before, an exhaustive understanding of
your business requirements. Since you mentioned only about Employees,
Addresses & storages and only the 1-to-many relationships between employees
and addresses as well as employees and storages, one way of logical
representation in SQL is :

CREATE TABLE Employees (
Emp_id INT NOT NULL PRIMARY KEY,
...);

CREATE TABLE EmployeeAddresses (
Address_id INT NOT NULL PRIMARY KEY,
Emp_id INT NOT NULL
REFERENCES Employees(Emp_id)
...);

CREATE TABLE EmployeeStorage (
Storage_id INT NOT NULL PRIMARY KEY,
Emp_id INT NOT NULL
REFERENCES Employees(Emp_id),
...);

Note, that it is quite possible that with a many-to-many relationship (an
address used by multiple employees or many employees sharing a single
storage), the above representation becomes under-normalized and thus
inadequate. In such a situation a better logical representation will be :

CREATE TABLE Employees (
Emp_id INT NOT NULL PRIMARY KEY,
...);

CREATE TABLE Addresses (
Address_id NOT NULL PRIMARY KEY,
...);

CREATE TABLE Storage (
Storage_id NOT NULL PRIMARY KEY,
...);

CREATE TABLE EmployeeAddresses (
Address_id INT NOT NULL
REFERENCES Addresses(Address_id)
Emp_id INT NOT NULL
REFERENCES Employees(Emp_id)
...
PRIMARY KEY (Emp_id, Address_id));

CREATE TABLE EmployeeStorage (
Storage_id INT NOT NULL
REFERENCES Storage(Storage_id)
Emp_id INT NOT NULL
REFERENCES Employees(Emp_id),
...
PRIMARY KEY (Emp_id, Storage_id));

As you can see, this logical model will be flexible enough to accomodate
changes without causing update/delete anomalies and can be considered
sufficiently normalized ( only based on the assumptions above, though ).

--
- Anith
( Please reply to newsgroups only )