0

A contrived example:

I have many Thimbles, identified in real-life by their color, that I lend out to friends. I want to know only the most recent friend that is wearing my Thimble. My friends covet my thimbles, so this information changes frequently.

Options I can think of are:

  1. Keep the lending information in the main thimble table
  2. Create a new table just for lending

Option 1 (1 Table with all info):

owned_thimble

Thimble ID Color Friend Lent Time
1 Blue Mary 2021-7-23
2 Green Paul 2021-6-13

Option 2 (2 Tables with constraints)

owned_thimble

Thimble ID Color
1 blue
2 green

lent_thimble

Thimble ID Friend Lent Time
2 Paul 2021-6-13
1 Mary 2021-7-23

When Paul inevitably returns my thimble, I'd want to lend it back out to another friend. When that happens, in Option A I am updating a row partially -- the Thimble ID is always associated with the same Colored thimble -- in Option B all the information related to lending a thimble is replaced.

Is either approach better or worse than the other?

2 Answers 2

2
  • I'd say option A is not normalized because neither friend nor lent time column describe the key (a given thimble identified with and ID).
  • Option B is correct and normal.
  • I would name the first table just THIMBLE and the second table THIMBLE_LENDING.
  • THIMBLE_LENDING actually represents an event (a thimble is lend) and that can be deduced by the fact that one of the columns is a timestamp.
  • For this to be completely normalised, I would create a third table FRIEND and have THIMBLE_LENDING have a FK pointing to it. That would prevent any update anomally.
  • Finally you can create a view to put it all back together for easy of use:
create view LENT_THIMBLES as
select
   t.thimble_id,
   t.color,
   l.lent_time,
   f.friend_name
from THIMBLE t 
join THIMBLE_LENDING l on (t.thimble_id = l.thimble_id)
join FRIEND f on (l.friend_id = f.friend_id);

Normalization is good for performance, avoids update or insert anomalies and makes growth easier like for example the need of keeping an historical record or all THIMBLE_LENDINGS wich is harder with the non-normal version.

0
  1. Performance I have seen a separation into two tables for users. A heavy-load, almost read-only table for fast authentications, and separately a table for updating a single user's properties.

  2. Performance The same separation also seen to have a second table of binary blobs, which may slow queries down.

  3. Normalisation An other reason for separation is normalisation. Not the case for a 1:1 relation.

One table is easier. An artificial join between two 1:1 tables is a form of antioptimization. Do not overengineer, until required.

2
  • I'm a little confused by your answer. Arre you recommending option 1? Option 2? Neither option? Jul 23, 2021 at 21:46
  • The three points are arguments, practical cases, in favor of separate tables. The last sentence states that one table is generally better though. Unclearly answered.
    – Joop Eggen
    Jul 24, 2021 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.