Free cookie consent management tool by TermsFeed Policy Generator

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

Last change on this file since 13508 was 10013, checked in by spimming, 11 years ago

#1888:

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