USE [CarDB]
GO

-- тип  покраски ТС
-- например: обычная, хамелеон, матовая, металик ...
CREATE TABLE [dbo].[tblVehOwnDICColoringType]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
	Value   nvarchar(16),      -- название типа покраски
	Comment nvarchar(max) NULL -- некое дополнительное пояснение	
)
GO

-- цвет покраски ТС - его описание 2-3 словами
-- например: кофе с молоком, navy, bianco, testa rossa, niger...
CREATE TABLE [dbo].[tblVehOwnDICColor]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
	Value   nvarchar(32),      -- название цвета, 2-мя - 3-мя словами 
	Comment nvarchar(max) NULL, -- некое дополнительное пояснение	
)
GO

-- тип владельца транспортного средства
-- например, физическое или юридическое лицо
CREATE TABLE [dbo].[tblVehOwnDICVehicleOwnerType]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
	Value   nvarchar(32) NOT NULL, -- это надпись которую видит (сразу) пользователь в GUI 
	Comment nvarchar(max) NULL     -- некое средней длины дополнительное пояснение
)
GO

-- страховая компания и ее параметры
CREATE TABLE [dbo].[tblVehOwnBOBKaskoInsurer]
(
	Id          uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
	CompanyName nvarchar(64) NOT NULL, -- это название компании которое видит пользователь в GUI 
	PhoneNumber nvarchar(12) NULL,     -- номер телефона call центра страховщика
	Comment     nvarchar(max) NULL     -- некое средней длины дополнительное пояснение	
)
GO

-- автомобильная страховка связана с владением (N страховок к 1-му владению)
CREATE TABLE [dbo].[tblVehOwnBOBKaskoInsurance]
(
    Id        uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
    Comment   nvarchar(max) NULL,    -- некое средней длины дополнительное пояснение	
	
    Number    nvarchar(32) NOT NULL, -- номер страховки
    Insurer   uniqueidentifier REFERENCES tblVehOwnBOBKaskoInsurer(Id) NOT NULL, -- страховая компания
    BeginDate date NULL, -- дата начала страхового периода
    EndDate   date NULL, -- дата окончания страхового периода
    
    -- владение во время которого застраховали
    Ownership uniqueidentifier NOT NULL, -- REFERENCES tblVehOwnBOBVehicleOwnership(Id)
    LastChangeDate datetime2
)
GO

-- владение конкретным экземпляром транспортного средства (N владений к 1-му ТС)
CREATE TABLE [dbo].[tblVehOwnBOBVehicleOwnership]
(
    Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
    Comment nvarchar(max) NULL, -- коментарий к этому владению 	
	
    Vehicle        uniqueidentifier NOT NULL, -- транспортное средство REFERENCES tblVehicleBOBVehicle(Id)
	
    VehRegNumber   nvarchar(10) NULL, -- государственный регистрационный номер ТС
    PassprotNumber nvarchar(10) NULL, -- номер пасспорта транспортного средатва (номер ПТС)
    BeginDate      date NOT NULL,     -- дата начала владения транспортным средством
    EndDate        date NULL,         -- дата окончания владения транспортным средством
    Color          uniqueidentifier REFERENCES tblVehOwnDICColor(Id) NOT NULL,        -- цвет покраски ТС 
    ColoringType   uniqueidentifier REFERENCES tblVehOwnDICColoringType(Id) NOT NULL, -- тип  покраски ТС  
        
    VehOwnerType   uniqueidentifier REFERENCES tblVehOwnDICVehicleOwnerType(Id) NOT NULL,-- тип владельца
    VehicleOwner   uniqueidentifier NOT NULL, -- владелец
    
    CurrKaskoInsurance uniqueidentifier REFERENCES tblVehOwnBOBKaskoInsurance(Id) NULL,-- текущая страх-ка
	
    DealerCompany uniqueidentifier NULL, -- ссылка на компанию, продавшую машину
	
    SybaseIdAc_car numeric(16,0),
    LastChangeDate datetime2
)
GO

USE [CarDB]
GO

-- данные водительских прав
-- кроме общей информации о человеке хранимой в tblPersonBOBPerson
CREATE TABLE [dbo].[tblPersonBOBDriverLicence]
(
	Id          uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Comment     nvarchar(max)			NULL, -- некое доп. пояснение
	
	Category    nchar(1)				NULL, -- категория прав - буква
	Series      nvarchar(8)				NULL, -- серия прав
	Number      nvarchar(16)			NOT NULL, -- номер прав
	WhoIsIssuer nvarchar(512)			NULL, -- кто  выдал	
	IssueDate   date					NULL, -- дата выдачи
	ExpireDate  date					NULL, -- дата окончания действия
	
	YearsOfExperience tinyint			NULL, -- стаж вождения

	PreviousSeries      nvarchar(8)		NULL, -- серия предыдущих прав
	PreviousNumber      nvarchar(16)	NULL, -- номер предыдущих прав
	LastChangeDate datetime2
)
GO

-- дополнительные паспортные данные
-- основные данные о человеке хранятся в tblPersonBOBPerson
CREATE TABLE [dbo].[tblPersonBOBPassport]
(
	Id          uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Comment     nvarchar(max)    NULL, -- некое доп. пояснение
	
	Series      nvarchar(8)  NOT NULL, -- серия паспорта
	Number      nvarchar(16) NOT NULL, -- номер паспорта
	IssueDate   date             NULL, -- дата выдачи
	WhoIsIssuer nvarchar(max)    NULL, -- кто выдал
	
	AddressOfResidence    uniqueidentifier NULL, -- адрес постоянной прописки
	AddressOfRegistration uniqueidentifier NULL, -- адрес временной регистрации
	LastChangeDate datetime2
)
GO

-- описывает пол персоны, пока их четыре: муж, жен, неизвестно, не важно
CREATE TABLE [dbo].[tblPersonDICGender]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value   nvarchar(16)  NOT NULL, -- краткое название пола
	Comment nvarchar(max) NULL  -- некое длинное пояснение
)
GO

-- семейное положение человека
CREATE TABLE [dbo].[tblPersonDICMaritalType]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value   nvarchar(24) NOT NULL,
	Comment nvarchar(max) NULL
)
GO

-- социальный статус человека: VIP, обычный лох
-- человек может быть клиентом, доверенным лицом и т.п.
CREATE TABLE [dbo].[tblPersonDICSocialStatus]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value   nvarchar(24)  NOT NULL, -- название типа подакции 
	Comment nvarchar(max) NULL  -- некое длинное пояснение
)
GO

-- человек (персона) в системе
CREATE TABLE [dbo].[tblPersonBOBPerson]
(
	Id              uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Comment         nvarchar(max)    NULL, -- некое доп. пояснение
	
	FirstName       nvarchar(64) NOT NULL, -- имя
	Surname         nvarchar(64) NOT NULL, -- фамилия
	PatronymicName  nvarchar(64)     NULL, -- отчество
	
	Gender 		uniqueidentifier REFERENCES tblPersonDICGender(Id) NULL,   -- пол 
	Marital		uniqueidentifier REFERENCES tblPersonDICMaritalType(Id) NULL,  -- семейный статус
	BirthDate       date             NULL,                                         -- дата рождения  
	Children        tinyint          NULL,                                         -- количество детей
	
	PrivateContacts uniqueidentifier NULL, -- свои контакты (без места)
	CurrLivingPlace uniqueidentifier NULL, -- текущее место жительства
	
        -- паспортные данные дополнительные
	AddPassportData uniqueidentifier REFERENCES tblPersonBOBPassport(Id)      NULL, 	

        -- данные водительских прав
	AddDriveLicData uniqueidentifier REFERENCES tblPersonBOBDriverLicence(Id) NULL, 

	SocialStatus    uniqueidentifier REFERENCES tblPersonDICSocialStatus      NULL, -- VIP или обычный
	
	SybaseIdAc_member numeric(16,0) NULL,
	LastChangeDate datetime2
)
go 

create index ind_tblPersonBOBPerson_Surname on tblPersonBOBPerson(Surname)
go