Update ERD about lightread HOT 4 CLOSED

Case Sensitivity:
Alright, if it's case insensitive we can stay as-is.
Payment hash as natural PK
Agree. Let's stick with artificial ID's.
Categories
Ok, we can go with this. The keyword family is then kind of the category. This just leads to articles being potentially shown in multiple categories (which is not bad).
Additional things I found:

tbl_Withdrawl

• add: fld_WTDR_Rhash
• remove fld_WTDR_TimeStamp and:
• add: fld_WTDR_CreationTimestamp (or CreationDate)
• add: fld_WTDR_SettleTimestamp (or SettleDate)
• add: fld_WTDR_Expiry (this is an integer field that defines the expiration of the invoice in seconds)

tbl_Invoice

• add: fld_INVC_CreationTimestamp (or CreationDate)
• add: fld_INVC_SettleTimestamp (or SettleDate)
• add: fld_INVC_Memo
• add: fld_INVC_Expiry (this is an integer field that defines the expiration of the invoice in seconds)

Hint: Regarding the Class Diagram it could make sense to make a parent class "Invoice" from which we inherit Withdrawl and Payment (since they are both invoices).

from lightread.

Agree! I will modify the ERD accordingly.

from lightread.

I am providing feedback to the ERD introduced in b419d87
Overall
Is PostgreSQL case sensitive? Would it make sense to stick to lower/uppercase for all table and field names?
tbl_Withdrawl and tbl_Invoice
Both entities have a lightning invoice as a base. It could, therefore, make sense to use the payment hash (r-hash) as a natural private key instead of the artificial ID.
Both tables lack the number of satoshis that is represented by the invoice.
tbl_User
We discussed having a deletion timestamp as well as a flag to indicate that the account is locked.
If we want to show the author of an article it would make sense to add the first name and last name or at least a nickname/username. For simplicity, all fields could be added to the user table.
Categories
We wanted to allow an article to be assigned to a specific category. Therefore, a tbl_Category should be provided (1:n to tbl_Content). Fields: ID, Name

Proposals (open for discussion)

• define clear structure regarding upper / lower case names
• Use the r-hash as natural PK for tbl_Withdrawl and tbl_Invoice
• add deletion timestamp (datetime) and active flag (boolean) to tbl_User
• add first_name, last_name, user_name to tbl_user
• amount field for tbl_Invoice and tbl_Withdrawl
• add table Category

from lightread.

Case Sensitivity:
Yes and no, as stated here. I don't mind going lower case everywhere in order to have a clear structure. What do you think?

Payment hash as natural PK
Technically possible. However, I recommend staying consistent and always adding an artificial ID to each table. So we don't have to distinguish which entity has what kind of PK (uniformity leads to simplicity).

Fee in Satoshi
Yep, missed that. I'm going to add it.

Further Fields on tbl_User
Totally agree. I’m going to add them.

Categories
I would say we use the keywords as categorization. That's why I suggested also to group specific keywords (by means of another 1:n table called tbl_KeywordFamily). For instance: the keywords {bitcoin, blockchain, ethereum} may belong to the keyword-group {crypto currencies} while {minimalism, vegetarianism, digital nomad} could be wrapped in the family {lifestyle}. In other words: the author's keyword selection determines the category.

from lightread.