Search code examples
databasedatabase-designdatabase-normalization

SQL Database Relationship

I have a DB design question,

I'm trying to design a DB-relationship for companies that have users and need to pay a monthly fee according to a formula. A formula has a name and fee but are not linked to an user but to a company.

Example:

Name: Formula 1

Fee: 5,00

Name: Formula 2

Fee: 10,00

Company XYZ has 2 users with Formula 1 and 2 users with Formula 2. They would need to pay 30,00 .

My initial solution was to link Formula's to users: enter image description here

But the problem I have discovered is that Formula's need to be linked to Companies and not to users. This because of the fact that different companies can have different fee's for the same formula.

Example: Users from company A with Formula 1 need to pay 5 but users from company B with Formula 1 need to pay 10.

This is where I met an obstacle because I didn't seem to have fully trust in my database design, in which I attempted to link Formula_Type with a company (and seperate fee...).

My attempt was to use groups:

enter image description here

But I faced a problem here: How or/and where would I split the fee of the Formula? Since they are depending on the company. Also, what foreign key would I use in the USER-table to link it to an Formula, or wouldn't this be possible in my case?

Is what I'm achieving even possible?

Solution

  • Try this on for size:

    Company
    ID              -- PK
    Name
    etc.

User
    ID              -- PK
    Company_ID      -- FK to Company
    Last_Name
    First_Name
    etc.

Formula
    ID              -- PK
    Name
    etc.

Formula_Company     -- this allows company-specific fees for different formulae
    ID              -- PK
    Formula_ID      -- FK to Formula
    Company_ID      -- FK to Company
    Fee

User_Formula_Company        -- this identifies which fee a given user is charged
    User_ID                 -- FK to User
    Formula_Company_ID      -- FK to Formula_Company