Free cookie consent management tool by TermsFeed Policy Generator

source: branches/OaaS/HeuristicLab.Services.Optimization.Billing.Test/DDLScript.sql @ 9641

Last change on this file since 9641 was 9641, checked in by spimming, 10 years ago

#1888:

  • Users now contain ContactInformation
  • Fixed formatting in invoice template
  • Fixed calculation for next invoice date
  • Worked on indentation for invoice fields
File size: 4.3 KB
Line 
1create 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);
13create table [dbo].[Invoices] (
14    [InvoiceId] [bigint] not null identity,
15    [UserId] [bigint] not null,
16    [OrderId] [bigint] not null,
17    [InvoiceDate] [datetime] not null,
18    [Due] [datetime] not null,
19    [StatusValue] [int] not null,
20    [Total] [float] not null,
21    [Tax] [float] not null,
22    [InvoiceDocument] [nvarchar](max) null,
23    primary key ([InvoiceId])
24);
25create table [dbo].[InvoiceLines] (
26    [InvoiceLineId] [bigint] not null identity,
27    [InvoiceId] [bigint] not null,
28    [ProductId] [bigint] not null,
29    [Quantity] [int] not null,
30    [ProductPrice] [float] not null,
31    primary key ([InvoiceLineId])
32);
33create table [dbo].[Orders] (
34    [OrderId] [bigint] not null identity,
35    [UserId] [bigint] not null,
36    [StateValue] [int] not null,
37    [BillingTypeValue] [int] not null,
38    [BillingPeriodValue] [int] not null,
39    [ActiveSince] [datetime] null,
40    [ActiveUntil] [datetime] null,
41    [LastBillableDay] [datetime] null,
42    [NextBillableDay] [datetime] null,
43    primary key ([OrderId])
44);
45create table [dbo].[OrderLines] (
46    [OrderLineId] [bigint] not null identity,
47    [OrderId] [bigint] not null,
48    [ProductId] [bigint] not null,
49    [Quantity] [int] not null,
50    [ProductPrice] [float] not null,
51    primary key ([OrderLineId])
52);
53create table [dbo].[PaymentInformations] (
54    [PaymentInformationId] [bigint] not null identity,
55    [UserId] [bigint] not null,
56    [CardNumber] [nvarchar](max) null,
57    [PaymentMethodValue] [int] not null,
58    [AdditionalInformation] [nvarchar](max) null,
59    primary key ([PaymentInformationId])
60);
61create table [dbo].[Products] (
62    [ProductId] [bigint] not null identity,
63    [Name] [nvarchar](max) null,
64    [Description] [nvarchar](max) null,
65    [ProductType] [nvarchar](max) null,
66    [Price] [float] not null,
67    primary key ([ProductId])
68);
69create table [dbo].[UsageRecords] (
70    [UsageRecordId] [bigint] not null identity,
71    [UserId] [bigint] not null,
72    [ProductId] [bigint] not null,
73    [Begin] [datetime] not null,
74    [End] [datetime] not null,
75    [ServiceIdentifier] [int] not null,
76    [ResourceIdentifier] [int] not null,
77    primary key ([UsageRecordId])
78);
79create table [dbo].[Users] (
80    [UserId] [bigint] not null identity,
81    [ContactInformationId] [bigint] not null,
82    [Name] [nvarchar](max) null,
83    primary key ([UserId])
84);
85alter table [dbo].[Invoices] add constraint [Invoice_Order] foreign key ([OrderId]) references [dbo].[Orders]([OrderId]);
86alter table [dbo].[Invoices] add constraint [Invoice_User] foreign key ([UserId]) references [dbo].[Users]([UserId]);
87alter table [dbo].[InvoiceLines] add constraint [InvoiceLine_Invoice] foreign key ([InvoiceId]) references [dbo].[Invoices]([InvoiceId]) on delete cascade;
88alter table [dbo].[InvoiceLines] add constraint [InvoiceLine_Product] foreign key ([ProductId]) references [dbo].[Products]([ProductId]);
89alter table [dbo].[Orders] add constraint [Order_User] foreign key ([UserId]) references [dbo].[Users]([UserId]);
90alter table [dbo].[OrderLines] add constraint [OrderLine_Order] foreign key ([OrderId]) references [dbo].[Orders]([OrderId]) on delete cascade;
91alter table [dbo].[OrderLines] add constraint [OrderLine_Product] foreign key ([ProductId]) references [dbo].[Products]([ProductId]);
92alter table [dbo].[UsageRecords] add constraint [UsageRecord_Product] foreign key ([ProductId]) references [dbo].[Products]([ProductId]);
93alter table [dbo].[UsageRecords] add constraint [UsageRecord_User] foreign key ([UserId]) references [dbo].[Users]([UserId]);
94alter table [dbo].[Users] add constraint [User_ContactInformation] foreign key ([ContactInformationId]) references [dbo].[ContactInformations]([ContactInformationId]);
95alter table [dbo].[PaymentInformations] add constraint [User_PaymentInformation] foreign key ([UserId]) references [dbo].[Users]([UserId]);
Note: See TracBrowser for help on using the repository browser.