Database anomalies and orphan tables

[Rebecca Blundell]

While investigating how to map out Mahara and see what connects to what a discovered a db schema-mapping tool. (Details in the bug report: https://bugs.launchpad.net/mahara/+bug/1855630)

That highlighted some issues with our db schema:

'Anomalies'
We have 3 tables that contain a single column:
artefact_comment_deletedby
event_type
view_type

We have 1 table with incrementing column names, potentially indicating denormalization
usr_friend

We have a number of orphan tables listed in the comment below.

[Rebecca Blundell (rjb-dev)]

List of orphan tables:

artefact_file_mime_types config
usr_access_roles site_data
artefact_license
blocktype_taggedposts_tags
oauth_server_nonce
usr_session external_functions
search_elasticsearch_queue
site_menublocktype_openbadgedisplayer_data
cron
sso_session
blocktype_externalfeed_data
skin_fonts

[Robert Lyon (robertl-9)]

Hi Rebecca,

With the 3 'Anomalies' we could fix up by adding an id column to the tables and then having that id be used in it's place else where, eg for view_type add an id column and then in view table in type column have the id rather than the word.

It would mean a bunch of changes and in the PHP code / SQL query structure to get things working right.

For the denormalized 'usr_friend' table it does kinda make sense to have usr1 and usr2 as they are users equal in weighting. Though we could change it to 'friend' and 'befriended' perhaps to make things clearer

[Robert Lyon (robertl-9)]

Hi Rebecca,

I'm not sure what is meant by orphaned tables - can you look into that and let me know what is meant there?

Cheers
Robert

[Rebecca Blundell (rjb-dev)]

Orphan tables are stand-alone tables without any foreign key relations, so they don't have any relationships with other tables. In theory, they shouldn't exist in a relational database, so I suppose that there are some foreign key constraints not set up that should be between these tables and others.

I had a look at blocktype_taggedposts_tags. The block_instance instance field in there looks to be the id from the block_instance table, however that relationship is not referenced in the database:

mahara-master=> select * from blocktype_taggedposts_tags;
-[ RECORD 1 ]--+-----
id | 1
block_instance | 38
tag | cats
tagtype | 1

mahara-master=> select * from block_instance
 where id=38;

-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------
id | 38
blocktype | taggedposts
title | Tagged journal entries
configdata | a:5:{s:5:"count";s:2:"10";s:8:"copytype";s:6:"nocopy";s:4:"full";b:0;s:11:"retractable";b:0;s:15:"retractedonload";b:0;}
view | 11
row |
column |
order |

mahara-master=> \d block_instance
                                      Table "public.block_instance"
   Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+--------------------------------------------
 id | bigint | | not null | nextval('block_instance_id_seq'::regclass)
 blocktype | character varying(255) | | not null |
 title | character varying(255) | | not null |
 configdata | text | | |
 view | bigint | | not null |
 row | smallint | | | 1
 column | smallint | | |
 order | smallint | | |
Indexes:
    "blocinst_id_pk" PRIMARY KEY, btree (id)
    "blocinst_blo_ix" btree (blocktype)
    "blocinst_vie_ix" btree (view)
Foreign-key constraints:
    "blocinst_blo_fk" FOREIGN KEY (blocktype) REFERENCES blocktype_installed(name)
    "blocinst_vie_fk" FOREIGN KEY (view) REFERENCES view(id)
Referenced by:
    TABLE "artefact_peer_assessment" CONSTRAINT "artepeerasse_blo_fk" FOREIGN KEY (block) REFERENCES block_instance(id)
    TABLE "block_instance_dimension" CONSTRAINT "blocinstdime_blo_fk" FOREIGN KEY (block) REFERENCES block_instance(id)
    TABLE "blocktype_wall_post" CONSTRAINT "blocwallpost_ins_fk" FOREIGN KEY (instance) REFERENCES block_instance(id)
    TABLE "framework_evidence" CONSTRAINT "framevid_ann_fk" FOREIGN KEY (annotation) REFERENCES block_instance(id)
    TABLE "view_artefact" CONSTRAINT "viewarte_blo_fk" FOREIGN KEY (block) REFERENCES block_instance(id)
    TABLE "watchlist_queue" CONSTRAINT "watcqueu_blo_fk" FOREIGN KEY (block) REFERENCES block_instance(id)

mahara-master=> \d blocktype_taggedposts_tags
                                        Table "public.blocktype_taggedposts_tags"
     Column | Type | Collation | Nullable | Default
---------------...

[Cecilia Vela Gurovic (ceciliavg)]

I looked into this and made comments for what I think we should do for each of the three issues

1. Single column tables: I couldn't find any reason for not having a single column table other than a table with a single column design can usually benefit from an additional column.

we need to see if the tables
artefact_comment_deletedby
event_type
view_type

are needed (should remain as a single column table) and make sure they don't need any data associated that could be added as a second column

2. incrementing column names: the only table affected would be 'usr_friend' because it has columns 'user1' and 'user2' . This would be a problem if those two columns had different meaning and taking one for the other would be a problem. But they represent friend relation between accounts, so it doesn't matter if a user is under user1 or user2
There is no need to change this table

3. orphaned tables (tables without any foreign key relations): we should check if any of the following table columns should be set up to be a foreign key
artefact_file_mime_types
config
usr_access_roles site_data
artefact_license
blocktype_taggedposts_tags
oauth_server_nonce
usr_session external_functions
search_elasticsearch_queue
site_menublocktype_openbadgedisplayer_data
cron
sso_session
blocktype_externalfeed_data
skin_fonts