Help me design a correct simple table

Question

Post reply

Help me design a correct simple table

freiza

SSC Enthusiast

Points: 118
More actions
August 13, 2022 at 6:20 pm
Go to Answer
#4076098

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?
Jonathan AC Roberts

SSCoach

Points: 18354
More actions
August 16, 2022 at 9:48 am
Answer
#4076874
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 11 (of 11 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

pietlinden

SSC Guru

Points: 63503

August 14, 2022 at 1:59 am

#4076152

Why not a separate table for each?

freiza SSC Enthusiast Points: 118 More actions · Answer 2

Like this?

DieselReceived(id, supplier, operator, taken_time)

DieselTaken(id, operator, machine, given_time)

Is this okay?

pietlinden SSC Guru Points: 63503 More actions · Answer 3

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

freiza SSC Enthusiast Points: 118 More actions · Answer 4

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.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 5

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)

freiza SSC Enthusiast Points: 118 More actions · Answer 6

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) ?

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 7

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)

freiza SSC Enthusiast Points: 118 More actions · Answer 8

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?

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 9

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

freiza SSC Enthusiast Points: 118 More actions · Answer 10

"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.