48 lines
1.9 KiB
SQL
48 lines
1.9 KiB
SQL
-- CreateTable
|
|
CREATE TABLE `Payer` (
|
|
`payer_id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`payer_name` VARCHAR(100) NOT NULL,
|
|
`payer_address` VARCHAR(255) NOT NULL,
|
|
`payer_abn` VARCHAR(15) NULL,
|
|
`payer_email` VARCHAR(100) NOT NULL,
|
|
|
|
PRIMARY KEY (`payer_id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `Invoice` (
|
|
`payer_id` INTEGER NOT NULL,
|
|
`invoice_suffix_code` INTEGER NOT NULL,
|
|
`invoice_payer_name` VARCHAR(100) NOT NULL,
|
|
`invoice_payer_address` VARCHAR(255) NOT NULL,
|
|
`invoice_payer_abn` VARCHAR(15) NULL,
|
|
`invoice_billing_period_start` DATETIME(3) NOT NULL,
|
|
`invoice_billing_period_end` DATETIME(3) NOT NULL,
|
|
`invoice_due_date` DATETIME(3) NOT NULL,
|
|
`invoice_date` DATETIME(3) NOT NULL,
|
|
`invoice_total_amount` DECIMAL(10, 2) NOT NULL,
|
|
`invoice_note` TEXT NULL,
|
|
|
|
PRIMARY KEY (`invoice_date`, `invoice_suffix_code`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `InvoiceItem` (
|
|
`item_id` INTEGER NOT NULL,
|
|
`invoice_date` DATETIME(3) NOT NULL,
|
|
`invoice_suffix_code` INTEGER NOT NULL,
|
|
`item_description` VARCHAR(255) NOT NULL,
|
|
`item_quantity` INTEGER NOT NULL,
|
|
`item_unit_price` DECIMAL(10, 2) NOT NULL,
|
|
`item_unit` VARCHAR(50) NOT NULL,
|
|
`invoice_id` INTEGER NOT NULL,
|
|
|
|
PRIMARY KEY (`item_id`, `invoice_date`, `invoice_suffix_code`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Invoice` ADD CONSTRAINT `Invoice_payer_id_fkey` FOREIGN KEY (`payer_id`) REFERENCES `Payer`(`payer_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `InvoiceItem` ADD CONSTRAINT `InvoiceItem_invoice_date_invoice_suffix_code_fkey` FOREIGN KEY (`invoice_date`, `invoice_suffix_code`) REFERENCES `Invoice`(`invoice_date`, `invoice_suffix_code`) ON DELETE RESTRICT ON UPDATE CASCADE;
|