2 Database Model
daniel edited this page 2026-04-18 10:41:23 +02:00

Einladung zu meinem miro-Board mit dem Diagramm und anderen Brainstorms

https://miro.com/welcomeonboard/L3hRTzRCVUxmamRWeVVmMTJXVEtNU1l4Uys5enNITmdWZG9JZE5BSEoveFhHK3UzMHB4QVUzOGkyVE9RWnNuY3d0MVRjVlh1SDlNQjZkTEVuWmVya29ZQ1l4amljZDZyNllILzRSa2FnWXFIRUU0OW5Nb0ZyNnh2cWlYOFBtSzRhWWluRVAxeXRuUUgwWDl3Mk1qRGVRPT0hdjE=?share_link_id=445102837807

erDiagram  
    Customers {
        UUID Id PK
        UUID TaxClassId FK
        UUID PriceGroupId FK
        UUID PaymentTermId FK
        UUID AddressId FK
        nvarchar(20) CustomerNumber "NOT NULL"
        nvarchar(100) FirstName
        nvarchar(100) LastName
        nvarchar(100) CompanyName
        nvarchar(50) PhoneNumber
        nvarchar(255) ContactPerson
        boolean NetGross "For what do we need this?"
        nvarchar(255) EmailAddress
        nvarchar(255) Homepage
    }
    Addresses {
        UUID Id PK
        UUID CountryId FK
        nvarchar(255) Street
        nvarchar(10) HouseNumber
        nvarchar(10) PostalCode
        nvarchar(255) City
    }
    Countries {
        UUID Id PK
        nvarchar(2) ISO2 "NOT NULL"
        nvarchar(50) Name "NOT NULL"
    }

    Articles {
        UUID Id PK
        UUID ArticleCategoryId FK
        nvarchar(100) ArticleNumber "NOT NULL"
        nvarchar(255) Name "NOT NULL"
        decimal ListPriceNet
        decimal PurchasePriceNet
    }
    ArticleCategories {
        UUID Id PK
        nvarchar(255) Name "NOT NULL"  
    }
    PriceCategories {
        UUID Id PK
        nvarchar(255) Name "NOT NULL; What is sthe purpose of this table? Couldn't we add this to ArticlePriceCategories?"
    }
    ArticlePriceCategories {
        UUID Id PK
        UUID ArticleId FK
        UUID PriceCategoryId FK
        decimal Price "NOT NULL"
    }

    TaxClasses {
        UUID Id PK
        nvarchar(50) Name "NOT NULL"
        decimal TaxRate "NOT NULL"
    }

    PaymentMethods {
            UUID Id PK
            nnvarchar(50) Name "NOT NULL"
            int DueDays "NOT NULL"  
    }

    DeliveryNotes {
            UUID Id PK
            UUID CustomerId FK
            nvarchar(20) DeliveryNoteNumber "NOT NULL"
            date Date "NOT NULL"
    }
    DeliveryNoteLines {
            UUID Id PK
            UUID DeliveryNoteId FK
            UUID Article_ID FK
            decimal Quantity "NOT NULL"
            decimal UnitPrice
    }

    Invoices {
        UUID Id PK
        UUID CustomerId FK
        nvarchar(20) InvoiceNumber "NOT NULL"
        date Date "NOT NULL"
        decimal TotalAmount
        boolean Paid
    }
    InvoiceLines {
        UUID Id PK
        UUID InvoiceId FK
        UUID ArticleId FK
        UUID TaxClass FK
        decimal Quantity "NOT NULL"
        decimal UnitPrice "NOT NULL"
    }

    DeliveryNotesInvoices {
        UUID Id PK
        UUID DeliveryNoteId FK
        UUID InvoiceId FK
    }

    Tenants {
        UUID Id PK
        nvarchar(255) Name
    }

    Users {
        UUID Id PK
        nvarchar(255) Name
        nvarchar(255) EmailAddress
    }

    Customers }o--|| PriceCategories : belongs_to
    Customers }o--|| PaymentMethods : uses
    Customers }o--|| TaxClasses : has
    Customers }o--|| Countries : has
    Customers }o--|| Addresses : has
    DeliveryNotes }o--|| Customers : has
    DeliveryNotes ||--o{ DeliveryNoteLines : contains
    DeliveryNoteLines }o--|| Articles : refers_to
    Invoices }o--|| Customers : has
    Invoices ||--o{ InvoiceLines : contains
    InvoiceLines }o--|| Articles : refers_to
    InvoiceLines }o--|| TaxClasses : has
    DeliveryNotesInvoices }o--|| DeliveryNotes : connects
    DeliveryNotesInvoices }o--|| Invoices : connects
    Articles }o--|| ArticleCategories : belongs_to
    Articles ||--o{ ArticlePriceCategories : has
    ArticlePriceCategories }o--|| PriceCategories : refers_to

    Tenants one to many UserTenants : has
    Users one to many UserTenants : belongs_to