Is it wise to separate relationship attributes with frequently repeated string values?

Question

Consider the relation SPACESHIP_FLAT:

╔═════╤════════════════╤════════════════╤═══════════╗
║ ID  │ NAME           │ TYPE           │ LV        ║
╟─────┼────────────────┼────────────────┼───────────╢
║ 1   │ Soyuz TMA-14   │ Soyuz          │ Soyuz-FG  ║
║ 2   │ Endeavour      │ Space Shuttle  │ Shuttle   ║
║ 3   │ Soyuz TMA-15M  │ Soyuz          │ Soyuz-FG  ║
║ 4   │ Atlantis       │ Space Shuttle  │ Shuttle   ║
║ 5   │ Soyuz TM-31    │ Soyuz          │ Soyuz-U   ║
║ 6   │ EFT-1          │ Orion          │ Delta-IV  ║
║ 7   │ XXX            │ CST-100        │ Delta-IV  ║
║ 8   │ YYY            │ CST-100        │ Falcon-9  ║
║ 9   │ ZZZ            │ Dragon V2      │ Falcon-9  ║
║ ... │ ...            │ ...            │ ...       ║
╚═════╧════════════════╧════════════════╧═══════════╝

Here the attributes TYPEand LVhave duplicate string values. So my question is: is it wise to project these attributes onto new relationships? The result will be as shown below.

SPACESHIP

╔═════╤════════════════╤═══════╤══════╗
║ ID  │ NAME           │ TYPE  │ LV   ║
╟─────┼────────────────┼───────┼──────╢
║ 1   │ Soyuz TMA-14   │ 1     │ 1    ║
║ 2   │ Endeavour      │ 2     │ 2    ║
║ 3   │ Soyuz TMA-15M  │ 1     │ 1    ║
║ 4   │ Atlantis       │ 2     │ 2    ║
║ 5   │ Soyuz TM-31    │ 1     │ 3    ║
║ 6   │ EFT-1          │ 3     │ 4    ║
║ 7   │ XXX            │ 4     │ 4    ║
║ 8   │ YYY            │ 4     │ 5    ║
║ 9   │ ZZZ            │ 5     │ 5    ║
║ ... │ ...            │ ...   │ ...  ║
╚═════╧════════════════╧═══════╧══════╝

SSTYPES

╔═════╤════════════════╗
║ ID  │ TYPE           ║
╟─────┼────────────────╢
║ 1   │ Soyuz          ║
║ 2   │ Space Shuttle  ║
║ 3   │ Orion          ║
║ 4   │ CST-100        ║
║ 5   │ Dragon V2      ║
║ ... │ ...            ║
╚═════╧════════════════╝

LVEHICLES

╔═════╤═══════════╗
║ ID  │ LV        ║
╟─────┼───────────╢
║ 1   │ Soyuz-FG  ║
║ 2   │ Shuttle   ║
║ 3   │ Soyuz-U   ║
║ 4   │ Delta-IV  ║
║ 5   │ Falcon-9  ║
║ ... │ ...       ║
╚═════╧═══════════╝

I normalized my relational model before 5NFand found no reason to separate TYPEand LVfrom the relationship (it does not cause update anomalies). But in the case of a huge number of tuples in the ratio, SPACESHIP_FLATit will consume a lot of resources (as I believe), so their separation will be a little more efficient. But I did not find it in database design theory.

Update

Ok, let's clarify some points.

TYPE NAME. NAME TYPE - . " -14" "" . , TYPE. NAME TYPE -- ( ).
LV NAME .
"" TYPE " -" " " TYPE, " -" " " .., "" , . "" ( ).
- , , (--, SPACESHIP_FLAT SPACESHIP - ).

Answer 1

, , , - . - .

SPACESHIP. SPACESHIP_FLAT.

5NF

, . 1NF.

• 1NF 2NF, , , , , "" 1NF 2NF.

• , , , , , 1NF 2NF, , 1NF 2NF.

• , Codd 3NF . BCNF, 4NF, 5NF NF, , , 3NF .

, .

• , , , , , . , nonse "".

• , ( ), .

• .. .

TYPE LV

, . , , .

• , post-Codd , "" , "SQL ..." "Relational ...". .

• 1NF.

• .

.

, . , , , ( ).

. , ( , , ) .

- , , , , ( ) , .

• UPDATE TYPE ID 5 SPACESHIP_FLAT, Soyuz Proto-Soyuz, 1 3 , . .

• , UPDATE TYPE ID 1 SSTYPES, Soyuz Proto-Soyuz, , SSTYPES. . .

• . , . , .

• NB. . .

SPACESHIP ( ),

, , . , , .. .. ..,

.

, , - . , , . .

, . , , .

, , .

, .

• .

  • , , , False , .

• ( , ), :

  • ( - , ),

.

, . Dr E F Codd. , , , ( ).

.

. . , , ( ), .

, , . ( ). , , , . , . , , , .

NAME. NAME TYPE - . " -14" "" . , TYPE.

( Relational terms, OO/ORM. , .)

NAME TYPE ( ).

. : , NAME TYPE, ( ).

.

• Lookup, , , , () , , , , .

• ( Lookup), subject , Dependency is Identify. PK PK. .

SPACESHIP TYPE, SPACESHIP (a) TYPE (b) TYPE PK SPACESHIP PK.

, SPACESHIP TYPE LAUNCH_VEHICLE, TYPE LAUNCH_VEHICLE SPACESHIP.

LV NAME .

. . NAME LAUNCH_VEHICLE (LV), .

"" " -" " ", " -" " " .., "" , . *

.

, 1NF. "" , . ( ). (a) (b) LAUNCH_VEHICLE (c) , . TMA vs TM-XX TYPE, .

, (c). ; , ; .. .

- Discovery, Endeavour, Volna, Yenesei, Lena .. , NAME, (TYPE, LAUNCH_VEHICLE XXXX).

Soyuz ( ).

. , . . , (, , ).

- , , (--, SPACESHIP_FLAT SPACESHIP - ).

• , " ". . , . , " " , , , , .

• , . .

• . . , , (, ) . , Relational Power, , , (, ).

  (Date, Darwen, Fagin, Abitebul, et al) . , () () .

--; ; --, , .

, , - C. J. Date. - , ( ).

. , . , .

, , , , , .., , , - . , , , , , , , .

, SO ( ). , - , , , , , "" " ". .

, , . . . .

, , , - E F Codd. , , . , , .

. J. Date , 5NF ( SPACESHIP_FLAT).

-- . . . Codd 1NF, 2NF, 3F. . (, , ).

"", "" . "" 3NF, . "" " " , " " ( ). , BCNF, 4NF, 5NF. , , , .

, NF, , , , . Date Darwen 1NF, , . .

( , ), 5NF .

, 3NF, 5NF, , . , , , 5NF , 5NF, . 3NF , "" 5NF, , .

?

() (.. , , , ) () (.. " " )

.

, "" BCNF, 4NF, 5NF, NF, " " . .

, 36 , BCNF, 4NF 5NF, 3NF ( 3NF ).

• , " ", Codd 3NF, , "", , "" . , "" " " .

, , Codd 3NF, , .

2007 , 95% DKNF, Codd . 5% - SQL. 2007 , , 100% DKNF.

• "" , . " ". Codd.

DKNF " " , , .

• () " " , () " ".

• .

• , , . .

, , - . , : , , : ( ); ( , JOIN power); . RFS--; RFS-; RFS.

:

• , , . , , .

  • NF. NF, .

  • , NF, Codd RM, , , , "" (a) (b) , , , .

• , , .

  • " " , () , , () , " " , , (c) " " , .

  • (, ), . . , , .

  • , " " - . , , . ( , ), "" -, - , . , , .

: ?

. . , ; ; , , . , RFS.

• . , . , .

• , - Post-Codd Record ID, , RM. RFS, , .

• , ID , , , ID , "". -.

- , JOINs?

? ? , , , . .

• IDEF1X. IDEF1X - , 1987 ( 1993 ). , , ., , "". , .

• , , ; ; ; ; ; ; - . IDEF1X. , .

• , PF. , (a) FK (b) , RoleNames.

• , . ( ). , . , Unix Inode ( , ), .

, "-", . ( , ) . "" - CHAR (X), , :

    SELECT  [Species]  = T.Name,
            [Activity] = A.Name
        FROM Species S
            JOIN Taxonomy T 
                ON S.SpeciesNo = T.TaxonomyNo
            JOIN SpeciesActivity SA
                ON S.SpeciesNo = SA.SpeciesNo
            JOIN Activity A
                ON SA.Activity = A.Activity

"--" , PRIMARY, VARCHAR?

. , , . , Relational, Key. ().

SpeciesActivity - .

• ID , (SpeciesNo, Activity) , ; , .

• .

• PK, , .

, , . VARCHAR . VARCHAR - .

• , , .

• VARCHAR Keys , , , / .

• , , , , . , . VARCHAR, , .

2

, , MySQL Workbench, , ?

, , freeware, myNONsql ( , ), , , .

. , . , , . , , . , , .

, ( SPACESHIP_FLAT), TYPE LV, SSTYPES LVEHICLES, FK.

.

, ; ID; SPACESHIP.TYPE LV . (a) () .

, . , , TYPE LV, FKs , , . , , CHAR (1) CHAR (2), .

• , ENUM ID.

• , , ( ) SPACESHIP ( JOIN Lookup).

  SSTYPE
  SS_Code Name
  ------- -------------
  Sz      Soyuz         
  SS      Space Shuttle 
  Or      Orion         
  C1      CST-100      
  D2      Dragon V2
  

SPACESHIP SS_Code LV_Code FK.

- [ual model] [al model].

, . , , : ) () ( "" , ).

• , . ​​ . ( - ).

• , ( ). , vs , . ERwin , CASE ; ; ; , -.

• , , . .

@PerformanceDBA , , , .

.

, : ?

. FK, (a) (b) , .

. "". . . , , . RM , -. , ( ) ..

• - "", , . , "", ( , , ) . . .

• "-", . .

• , "" - , , ( ), "", ( ). , , () () () ( ) " ". , , , , "".

:

?

• - .

  , . ( , .)

• , , .

  , () .

Answer 2

, . , , . , Blabla Michael Jordan. - , .

, -, :

NULLs, , NULLs BL, NULL .

.

Answer 3

SPACESHIP_FLAT ( ), .

. " - ". , DMBS ; () . , , .

, , , , , . ( .)

.

This is not part of normalization. Normalization does not introduce new columns. But this is not all that is needed for a good design.