Help me design a correct simple table

  • I have a table like

    Diesel(id, supplier, operator, machine, taken_time, given_time)

    There are 2 kinds of transaction:

    1) Suppliers gives diesel to operator

    2) Operator gives diesel to machine

    My problem is when supplier gives diesel to operator then the machine is null and when operator gives diesel to machine then supplier column is null,

    but I don't want to make supplier and machine column to accept null. How do I partition my table?

  • Why not a separate table for each?

  • Like this?

    DieselReceived(id, supplier, operator, taken_time)

    DieselTaken(id, operator, machine, given_time)

    Is this okay?

  • looks okay if you're talking about the basic design. What did you intend to do with it?

  • I intend to learn database design. I watched 1NF, 2NF, 3NF and BCNF on youtube. But still I am not able to understand it properly and cannot use them correctly. So I thought I would create some hypothetical situations and try to model it. And ask experienced people if I did it correctly. This way I might learn and understand. So if you can improve it more further then it will help me even more.

    For here I was trying to model this scenario (completely made up)

    A company has several products some of them it buys from suppliers for its internal use. Some of them it produces and sells.

    The bought products are given to some company members.

    The bought products have different attributes, some of them are liquids and measured in litres while other are solid and measured in Kilogram.

     

  • How do you intend to track "transactions"?  I seem to be that the operator gets a "batch" of fuel and then the operator give that fuel to a single machine.  That would be two subtransactions within a main transaction.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Yes, I was thinking about that too. But I don't know what should be in the 3rd main table.

    Should it be

    MainTable(id, DieselReceived_ID, DieselTaken_ID)  ?

  • That certainly a possibility.  That's more of a "bridge" table.  I don't know what else you're trying to track, though.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • You got everything correctly over here

    "How do you intend to track "transactions"?  I seem to be that the operator gets a "batch" of fuel and then the operator give that fuel to a single machine.  That would be two subtransactions within a main transaction."

    I only need to capture this scenario. Are there any other or more elegant ways to do that?

  • Why don't you want to make supplier and machine column accept null?

  • "Why don't you want to make supplier and machine column accept null?"

    Because I think it would be incorrect.

    One of the transaction is that Supplier gives fuel to operator  so supplier can't be null.

    And same goes for machine.

  • You could add a constraint to the table to ensure that one of the values is null and one is not null:

    ALTER TABLE DieselTransaction
    ADD CONSTRAINT CheckOneColumnIsNull
    CHECK((CASE WHEN Suppliers IS NULL THEN 0 ELSE 1 END
    + CASE WHEN Operator IS NULL THEN 0 ELSE 1 END) = 1
    );
    GO

    Or maybe instead add a bit column to specify if the column value is a supplier or an operator.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply