# Current Analysis of Referential Integrity in the FRPG Database Tables.
Here is where analysis will be done on the current state of the database and the relations between the tables, we are mainly analyzing the referential integrity (i.e. a table has appropriate cascading deletion and foreign key constraints).
After analysis is done, the tables should be sub divided into three categories:
**No Change Needed**: The table handles referential integrity properly.
**Some Change May Be Needed**: Their is some ambiguity as to whether or not referential integrity is handled properly here, and may require additional investigation/some debate as to what, if anything, should be changed.
**Change Needed**: The table has blatant issues with referential integrity that should be fixed in the future to ensure proper database practices.
## No Change Needed
* Crews
- This table is fine, there are no foreign keys in it, so it does not have to enforce any kind of referential integrity.
* DubloonPrizes
- This table is also fine, there are no foreign keys in it, so it does not have to enforce any kind of referential integrity.
* VanityAwards
- This is a well designed table, it enforces referential integrity between it and the VanityItems and Quests tables. It also enforces a cascading delete.
* VanityInventory
- Again, this is a well made table it enforces foreign key constraints on the Player and VanityItems tables that it references. It also has cascading deletion.
VanityItems
- This is a parent table with no references to other tables, so no referential integrity must be upheld in this case.
* VisitedMaps
- This table has the proper foreign key constraints to Player : playerID and Server : mapID, and has cascading deletion.
* Levels
- This table has no foreign keys.
* NPCQuestions
- This table has no foreign keys.
* NPCs
- This is a table directly related to the Player table; it seems to properly handle referential integrity. There is a foreign key constraint to the Player table and an ON DELETE CASCADE statement as well. A bit nitpicky but I feel like the column playerID should be called npcID just for the sake of distinction.
* PlayerConnections
- Everything here is done correctly, there is a foreign key constraint on the playerID it stores and an on delete cascade statement.
* PlayerLogins
- This is also a well made table; the playerID that is referenced here is part of a foreign key constraint and has an on delete cascade statement attached as well.
* Quest
- This is also a parent table so it is creating the things that other tables will have to reference. Again, this table, like the InteractableItems table, stores a triggerMapName (Ref. Server: mapName). It may be wise to have a foreign key and cascading delete here, if we want quests to be deleted when a server is deleted (which most likely is not the case, but it is an option).
* Server
- This is a parent table and does not reference any other table so there is no need for any foreign key constraints.
## Some Change May Be Needed
* InteractableItems
- This table does not have any foreign keys, although it may be worth mentioning that it does store a mapName, which is a key stored in the Server table. It might be prudent to add a foreign key constraint and cascading delete on this column in the (rare) case a Server is deleted.
* Majors
- This table has no foreign keys, but one thing worth mentioning is that the majorID is not defined as a primary key. Most likely does not matter, but might be worth adding.
* Objectives
- This table references the Quests : questID, but there is no foreign key constraint or cascading delete statement present. The questID is used in the primary key of the table. This most likely is done intentionally, so it may not be necessary to enforce referential integrity here.
## Change Needed
* Player
- Given that this is a parent table it does not have much referential integrity to worry about. However, the crew and major columns are references to the primary key of the Crew and Major tables. It might be smart to add a foreign key constraint to ensure we are actually referencing an existing row in these tables, an on delete cascade here seems reckless. If a major is deleted the players of the major should not be, and the Major table seems to be fairly set-in-stone.
* QuestStates
- This table is very similar to the ObjectiveStates table in that it has multiple foreign keys to different tables; Player: playerID, Quests: questID. Neither of which have a foreign key constraint imposed on them (and therefore no cascading deletion). These constraints should exist here because if we for whatever reason deleted a player, we would also want to get rid of everything associated with them, their QuestsStates fall within this purview.
* RandomFacts
- This table makes reference to the NPCs table’s playerID. There is not a foreign key constraint present here. There should be though, and a cascading delete, if we delete an NPC from the game we want to delete the facts they spew if such facts exist.
* Timers
- This table references Player : playerID, but does not have a foreign key constraint or cascading delete, when it should. Again, if we were to delete a player we would want to delete any of their timers as well.
* Friends
- There are two columns that reference the Player table’s primary key.
playerID, and FRIENDID, neither of which have a foreign key constraint imposed on them. Also, they are not deleted on cascade.
* ObjectiveStates
- This table has 3 columns that reference other tables: Objectives : objectiveID, Quests : questID, and Player : playerID. However none of these have a foreign key constraint on them, which may be due to the fact that quests, players, or objectives are very rarely, if ever, deleted..
