Adding to my database?
Mar. 27th, 2026 10:18 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
artemisdartIt occurred to me that the people I've given and received gifts for are getting difficult to remember with my fleshy human brain. I've joined a several dozen exchanges, and it's hard for me to keep everyone's aliases in mind! So maybe I should add a few tables to my database to account for this.
I already have a Dim_Work table for the things I've written, and a Dim_User table to store info on users, and those are joined by a Fact_Kudos table to link a user to a work by the date of the kudos they left me.
I could add a new Fact table for Dim_GiftWork, which would mark the date, exchange, and user who gave me a gift, along with what date the gift was revealed. (I don't care what date they posted their gift; their wordcount over time means nothing to me, so I wouldn't track that.)
So I think I'm going to add the following:
Dim_Exchange
~~~
Dim_Ship
~~~
Bridge_WorkShip
This bridge table is necessary because a single work can have more than one ship, and of course a single ship can exist in more than one work. So it's a classic Many-to-Many relationship, which causes ALL sorts of problems if the data isn't normalized into a nice bridge table like this one. :)
~~~
Bridge_WorkRecipient
This bridge table links Dim_Work → Dim_User to track the recipient(s) of works I wrote as gifts (there can be more than one).
~~~
Dim_GiftWork
This table would be for gifts that were given to me.
~~~
Fact_GiftWork
(I might add this later -- it's optional. This table would be helpful if I needed easier metrics over time, reporting on the gifts given to me. I don't think I really need it -- I haven't received THAT many gift works!)
I already have a Dim_Work table for the things I've written, and a Dim_User table to store info on users, and those are joined by a Fact_Kudos table to link a user to a work by the date of the kudos they left me.
I could add a new Fact table for Dim_GiftWork, which would mark the date, exchange, and user who gave me a gift, along with what date the gift was revealed. (I don't care what date they posted their gift; their wordcount over time means nothing to me, so I wouldn't track that.)
So I think I'm going to add the following:
Dim_Exchange
- Exchange_key (PK) - Surrogate key
- Exchange_name - e.g., “Yuletide”
- Exchange_type - Optional (e.g., “Secret Santa”, “Big Bang”)
- Exchange_year - Useful for reporting
- Exchange_notes - freeform in case I have notes to store
~~~
Dim_Ship
- Ship_key (PK) - Surrogate key
- Ship_name - e.g., "Gingerpilot"
- Ship_fandom - I might make this into a table of its own later
- Ship_notes - for alternate names, tags, or clarifications
~~~
Bridge_WorkShip
- Work_key (FK) - Links to Dim_Work
- Ship_key (FK) - Links to Dim_Ship
This bridge table is necessary because a single work can have more than one ship, and of course a single ship can exist in more than one work. So it's a classic Many-to-Many relationship, which causes ALL sorts of problems if the data isn't normalized into a nice bridge table like this one. :)
~~~
Bridge_WorkRecipient
- Work_key (FK) - Links to Dim_Work
- Recipient_user_key (FK) - Links to Dim_User
This bridge table links Dim_Work → Dim_User to track the recipient(s) of works I wrote as gifts (there can be more than one).
~~~
Dim_GiftWork
- GiftWork_key (PK) - Surrogate key
- GiftWork_title
- GiftWork_exchange_key (FK) - Links to Dim_Exchange
- GiftWork_ship_key (FK) - Links to Dim_Ship
- GiftWork_author_key (FK) - Links to Dim_User
- GiftWork_reveal_date - The date the gift was revealed
This table would be for gifts that were given to me.
~~~
Fact_GiftWork
- GiftFact_key (PK) - Surrogate key
- GiftWork_key (FK) - The gift work
- Recipient_user_key (FK) - The user I wrote it for
- Reveal_date - helps with reporting over time
(I might add this later -- it's optional. This table would be helpful if I needed easier metrics over time, reporting on the gifts given to me. I don't think I really need it -- I haven't received THAT many gift works!)
