BenTBCampbell
commented
3 years ago Well, we added the tables, we just need to add constraints now. Here's some ideas:
Order of creating entries:
Here's a list of what ids each table relies on. This is useful for designing constraints.
- ContactInfo: nothing
- Word: nothing
- Account: ContactInfo
- Definition: Word, (Account, but may be 0)
- Comment: Account, Definition, (optional Comment for reply. 0 if not a reply?)
Constraints
Everything
- adding: obviously all the ids must be unique
Word
- adding: word_name must be unique
- deleting: Can't be deleted by anyone unless they're the person who made it or an admin. If it doesn't have an owner (account_id is 0), it can only be deleted by an admin.
Definition
- adding: word must exist
- deleting: if it's the last definition for the word, delete the word as well.
Comment
- adding: can't be made unless the account and definition also exist. If it is a reply, the comment it's replying to must also exist
- deleting: If it's deleted, all the comments replying to it are deleted too (maybe. This could require more discussion)
Account
- adding: username and contactinfo _id must be unique
- deleting: when it's deleted, all the definitions for the account are given an account_id of 0 (which would basically mean no account), and its contact info must also be deleted.
ContactInfo
- adding: email and phone must be unique
I don't know about SQL to implement this, but that's at least a list of the constraints I can think of. Does that help @usa4cc ?
We need to properly design the database tables so that the Account class can determine the Definitions, Comments, Followed Accounts, and Upvotes. FollowedAccounts and Upvotes specifically, will need additional implementation.
We also need to add proper constraints for adding/deleting items like words/definitions.