[9602] | 1 | create table [dbo].[ContactInformations] (
|
---|
| 2 | [ContactInformationId] [bigint] not null identity,
|
---|
| 3 | [OrganizationName] [nvarchar](max) null,
|
---|
| 4 | [Street] [nvarchar](max) null,
|
---|
| 5 | [City] [nvarchar](max) null,
|
---|
| 6 | [State] [nvarchar](max) null,
|
---|
| 7 | [PostalCode] [nvarchar](max) null,
|
---|
| 8 | [LastName] [nvarchar](max) null,
|
---|
| 9 | [FirstName] [nvarchar](max) null,
|
---|
| 10 | [Email] [nvarchar](max) null,
|
---|
| 11 | primary key ([ContactInformationId])
|
---|
| 12 | );
|
---|
| 13 | create table [dbo].[Invoices] (
|
---|
| 14 | [InvoiceId] [bigint] not null identity,
|
---|
| 15 | [UserId] [bigint] not null,
|
---|
| 16 | [OrderId] [bigint] not null,
|
---|
| 17 | [Due] [datetime] not null,
|
---|
| 18 | [StatusValue] [int] not null,
|
---|
| 19 | [InvoiceDocument] [nvarchar](max) null,
|
---|
| 20 | primary key ([InvoiceId])
|
---|
| 21 | );
|
---|
| 22 | create table [dbo].[InvoiceLines] (
|
---|
| 23 | [InvoiceLineId] [bigint] not null identity,
|
---|
| 24 | [InvoiceId] [bigint] not null,
|
---|
| 25 | [ProductId] [bigint] not null,
|
---|
| 26 | [Quantity] [int] not null,
|
---|
| 27 | [ProductPrice] [float] not null,
|
---|
| 28 | primary key ([InvoiceLineId])
|
---|
| 29 | );
|
---|
| 30 | create table [dbo].[Orders] (
|
---|
| 31 | [OrderId] [bigint] not null identity,
|
---|
| 32 | [UserId] [bigint] not null,
|
---|
| 33 | [StateValue] [int] not null,
|
---|
| 34 | [BillingTypeValue] [int] not null,
|
---|
| 35 | [BillingPeriodValue] [int] not null,
|
---|
| 36 | [ActiveSince] [datetime] null,
|
---|
| 37 | [ActiveUntil] [datetime] null,
|
---|
| 38 | primary key ([OrderId])
|
---|
| 39 | );
|
---|
| 40 | create table [dbo].[OrderLines] (
|
---|
| 41 | [OrderLineId] [bigint] not null identity,
|
---|
| 42 | [OrderId] [bigint] not null,
|
---|
| 43 | [ProductId] [bigint] not null,
|
---|
| 44 | [Quantity] [int] not null,
|
---|
| 45 | [ProductPrice] [float] not null,
|
---|
| 46 | primary key ([OrderLineId])
|
---|
| 47 | );
|
---|
| 48 | create table [dbo].[PaymentInformations] (
|
---|
| 49 | [PaymentInformationId] [bigint] not null identity,
|
---|
| 50 | [UserId] [bigint] not null,
|
---|
| 51 | [CardNumber] [nvarchar](max) null,
|
---|
| 52 | [PaymentMethodValue] [int] not null,
|
---|
| 53 | [AdditionalInformation] [nvarchar](max) null,
|
---|
| 54 | primary key ([PaymentInformationId])
|
---|
| 55 | );
|
---|
| 56 | create table [dbo].[Products] (
|
---|
| 57 | [ProductId] [bigint] not null identity,
|
---|
| 58 | [Name] [nvarchar](max) null,
|
---|
| 59 | [Description] [nvarchar](max) null,
|
---|
| 60 | [ProductType] [nvarchar](max) null,
|
---|
| 61 | [Price] [float] not null,
|
---|
| 62 | primary key ([ProductId])
|
---|
| 63 | );
|
---|
| 64 | create table [dbo].[UsageRecords] (
|
---|
| 65 | [UsageRecordId] [bigint] not null identity,
|
---|
| 66 | [UserId] [bigint] not null,
|
---|
| 67 | [ProductId] [bigint] not null,
|
---|
| 68 | [Begin] [datetime] not null,
|
---|
| 69 | [End] [datetime] not null,
|
---|
| 70 | [ServiceIdentifier] [int] not null,
|
---|
| 71 | [ResourceIdentifier] [int] not null,
|
---|
| 72 | primary key ([UsageRecordId])
|
---|
| 73 | );
|
---|
| 74 | create table [dbo].[Users] (
|
---|
| 75 | [UserId] [bigint] not null identity,
|
---|
| 76 | [Name] [nvarchar](max) null,
|
---|
| 77 | primary key ([UserId])
|
---|
| 78 | );
|
---|
| 79 | alter table [dbo].[Invoices] add constraint [Invoice_Order] foreign key ([OrderId]) references [dbo].[Orders]([OrderId]);
|
---|
| 80 | alter table [dbo].[Invoices] add constraint [Invoice_User] foreign key ([UserId]) references [dbo].[Users]([UserId]);
|
---|
| 81 | alter table [dbo].[InvoiceLines] add constraint [InvoiceLine_Invoice] foreign key ([InvoiceId]) references [dbo].[Invoices]([InvoiceId]) on delete cascade;
|
---|
| 82 | alter table [dbo].[InvoiceLines] add constraint [InvoiceLine_Product] foreign key ([ProductId]) references [dbo].[Products]([ProductId]);
|
---|
| 83 | alter table [dbo].[Orders] add constraint [Order_User] foreign key ([UserId]) references [dbo].[Users]([UserId]);
|
---|
| 84 | alter table [dbo].[OrderLines] add constraint [OrderLine_Order] foreign key ([OrderId]) references [dbo].[Orders]([OrderId]) on delete cascade;
|
---|
| 85 | alter table [dbo].[OrderLines] add constraint [OrderLine_Product] foreign key ([ProductId]) references [dbo].[Products]([ProductId]);
|
---|
| 86 | alter table [dbo].[UsageRecords] add constraint [UsageRecord_Product] foreign key ([ProductId]) references [dbo].[Products]([ProductId]);
|
---|
| 87 | alter table [dbo].[UsageRecords] add constraint [UsageRecord_User] foreign key ([UserId]) references [dbo].[Users]([UserId]);
|
---|
| 88 | alter table [dbo].[PaymentInformations] add constraint [User_PaymentInformation] foreign key ([UserId]) references [dbo].[Users]([UserId]);
|
---|