Entities/data modeling

[jtkaufman737]

Starting to think about basic data models, I originally thought of this idea as like trello for fault tree analysis, so in my mind I've been thinking of a specific connected graphic as a "board". We can change that in the future of course. The other relevant elements would be events, gates, and transfers. All of those elements have relationships to one another, and back to the board to indicate their membership to that board. Roughly, here's some data I've thought of them each containing:

• Board: I don't think we'll have time for auth, so I don't think there will be ownership for boards. I do think they will need an ID, a name, maybe a description
• Event symbols: Will have a type, a description, an ID, and then a board
• Gate symbols: Will have a type, a description, an ID, and a board
• Transfer symbols: Will have a type, a description, an ID, a parent board and a related board
• Connections: This would be what I've heard called a "bridge" table in SQL world. Connections would represent the lines between items

Translating this into actual tables gets a little dicey. So far the best way of accomplishing this (imo) might be something like the following (with PK meaning primary key, and FK meaning foreign key):

Board table id (PK) name description

Board symbols board_id (FK) symbol_id (FK) (Could make PK as compound key on both fields to ensure uniqueness)

Symbols id (PK) name type (FK) child_board (FK) <- but only transfer symbols could have that

Symbol types id (FK) type (this would contain all the options like "Event/basic" or "Gate/and")

Symbol connections board_id (FK) start_symbol (FK) destination_symbol (FK) (Could make PK as compound key on both fields to ensure uniqueness)

Rationale: Part of me wanted to make events, gates, and transfers their own entity because transfers are the only one to have a secondary board field. (Transfers represent when one board references or transitions into another fault tree analysis board). However, this makes us run into a problem when we consider the symbol connections. I don't know of a way to set that up to allow foreign keys from multiple respective tables. So that would leave us for splitting into separate tables for event symbol connections, gate symbol connections, and transfer symbol connections. That to me seems too complicated when it comes to how we will render that data visually. LMK if that doesn't make sense, this may be an exercise best accomplished over a call

[jtkaufman737]

That of course assumes we are using a relational model, if we want to use NoSQL this would look completely different. I have some NoSQL experience and TBH trying to model this data that way seems like an absolute nightmare, but if someone sees a compelling reason for it can discuss

[jtkaufman737]

Done, database work underway