Severity: 8192
Message: Creation of dynamic property CI_URI::$config is deprecated
Filename: core/URI.php
Line Number: 101
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Router::$uri is deprecated
Filename: core/Router.php
Line Number: 127
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: Warning
Message: Cannot modify header information - headers already sent by (output started at /home/storkfamilyuser/codeigniter/system/core/Exceptions.php:271)
Filename: core/Security.php
Line Number: 272
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$benchmark is deprecated
Filename: core/Controller.php
Line Number: 75
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$hooks is deprecated
Filename: core/Controller.php
Line Number: 75
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$config is deprecated
Filename: core/Controller.php
Line Number: 75
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$log is deprecated
Filename: core/Controller.php
Line Number: 75
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$utf8 is deprecated
Filename: core/Controller.php
Line Number: 75
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$uri is deprecated
Filename: core/Controller.php
Line Number: 75
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$exceptions is deprecated
Filename: core/Controller.php
Line Number: 75
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$router is deprecated
Filename: core/Controller.php
Line Number: 75
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$output is deprecated
Filename: core/Controller.php
Line Number: 75
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$security is deprecated
Filename: core/Controller.php
Line Number: 75
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$input is deprecated
Filename: core/Controller.php
Line Number: 75
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$lang is deprecated
Filename: core/Controller.php
Line Number: 75
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$load is deprecated
Filename: core/Controller.php
Line Number: 78
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Return type of CI_Session_database_driver::open($save_path, $name) should either be compatible with SessionHandlerInterface::open(string $path, string $name): bool, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice
Filename: drivers/Session_database_driver.php
Line Number: 129
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Return type of CI_Session_database_driver::close() should either be compatible with SessionHandlerInterface::close(): bool, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice
Filename: drivers/Session_database_driver.php
Line Number: 278
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Return type of CI_Session_database_driver::read($session_id) should either be compatible with SessionHandlerInterface::read(string $id): string|false, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice
Filename: drivers/Session_database_driver.php
Line Number: 149
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Return type of CI_Session_database_driver::write($session_id, $session_data) should either be compatible with SessionHandlerInterface::write(string $id, string $data): bool, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice
Filename: drivers/Session_database_driver.php
Line Number: 206
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Return type of CI_Session_database_driver::destroy($session_id) should either be compatible with SessionHandlerInterface::destroy(string $id): bool, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice
Filename: drivers/Session_database_driver.php
Line Number: 295
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Return type of CI_Session_database_driver::gc($maxlifetime) should either be compatible with SessionHandlerInterface::gc(int $max_lifetime): int|false, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice
Filename: drivers/Session_database_driver.php
Line Number: 333
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: Warning
Message: ini_set(): Session ini settings cannot be changed after headers have already been sent
Filename: Session/Session.php
Line Number: 284
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: Warning
Message: session_set_cookie_params(): Session cookie parameters cannot be changed after headers have already been sent
Filename: Session/Session.php
Line Number: 291
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: Warning
Message: ini_set(): Session ini settings cannot be changed after headers have already been sent
Filename: Session/Session.php
Line Number: 316
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: Warning
Message: ini_set(): Session ini settings cannot be changed after headers have already been sent
Filename: Session/Session.php
Line Number: 317
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: Warning
Message: ini_set(): Session ini settings cannot be changed after headers have already been sent
Filename: Session/Session.php
Line Number: 318
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: Warning
Message: ini_set(): Session ini settings cannot be changed after headers have already been sent
Filename: Session/Session.php
Line Number: 319
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: Warning
Message: ini_set(): Session ini settings cannot be changed after headers have already been sent
Filename: Session/Session.php
Line Number: 377
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$db is deprecated
Filename: core/Loader.php
Line Number: 390
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_DB_mysqli_driver::$failover is deprecated
Filename: database/DB_driver.php
Line Number: 371
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: Warning
Message: session_set_save_handler(): Session save handler cannot be changed after headers have already been sent
Filename: Session/Session.php
Line Number: 110
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: Warning
Message: session_start(): Session cannot be started after headers have already been sent
Filename: Session/Session.php
Line Number: 143
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$session is deprecated
Filename: core/Loader.php
Line Number: 1277
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$encryption is deprecated
Filename: core/Loader.php
Line Number: 1277
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$securityservice is deprecated
Filename: core/Loader.php
Line Number: 353
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$navservice is deprecated
Filename: core/Loader.php
Line Number: 353
Backtrace:
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$benchmark is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$hooks is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$config is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$log is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$utf8 is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$uri is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$exceptions is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$router is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$output is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$security is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$input is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$lang is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$load is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$db is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$session is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$encryption is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$securityservice is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$navservice is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property CI_Loader::$form_validation is deprecated
Filename: core/Loader.php
Line Number: 925
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/views/page.php
Line: 16
Function: view
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
Severity: 8192
Message: Creation of dynamic property About::$form_validation is deprecated
Filename: core/Loader.php
Line Number: 1277
Backtrace:
File: /home/storkfamilyuser/codeigniter/application/views/include/nav.php
Line: 11
Function: library
File: /home/storkfamilyuser/codeigniter/application/views/page.php
Line: 13
Function: view
File: /home/storkfamilyuser/codeigniter/application/controllers/About.php
Line: 8
Function: view
File: /home/storkfamilyuser/stork.family/index.php
Line: 315
Function: require_once
One of the most popular databases is MySQL, and for several good reasons. It's free. It's fast. It's the database behind the majority of the internet. I've been using MySQL for years.
MySQL is a Relational Database which means that it is based on the Relational Model. Contrary to popular belief, the parent/child relationships commonly created in databases are not the reason it is called Relational. A Relation is represented as a Table with rows and columns. It is not the relationships between data.
Relational databases do a lot of things very well. I love them. They're my job and my hobby. And while it's easy to store things like This person is the father of these people in a relational database, it's tough to answer questions like "How is my son related to my great grandmother's second cousin's wife's sister?" You can do it. It's not easy.
A relational database is not the best choice for tracking relationships between data. There are graph databases based on Graph Theory that are much better suited. In graph databases, the relationship between data is treated very similarily to the data itself. I've used and love Neo4j, a free, light-weight, powerful graph database. It exceeded my expectations by a wide margin and I found it easy to learn and delightful to use. It's just not nearly as available to be used as a web page back end database as MySQL is. Especially when I'm using the least expensive Dreamhost plan available.
There are conceptually only two objects in a graph database: the Vertex and the Edge. For my geneoalogy project, a person is a Vertex and their relationships to other people are Edges. These objects can be modeled in a relational database (as tables). Graph databases have some graph-specific functions that make finding relationships fast and simple. These functions are lacking in relational databases.
In my model, there are two types of edges:
This site is an attempt to track my real genealogy data in graph structures modeled in a relational database. It is also an attempt to mimic graph-specific functions in a relational database.
I use whatever version of MySQL my host has provided as my relational database. I'm using PHP as the primary programming language. I'm using the CodeIgniter Version 3.x as the MVC framework for the web pages. Additionally, I'm using the CSS framework created by W3Schools.com (which I find easier to understand than Bootstrap).
Apart from my regular, least-expensive internet hosting plan, I haven't spent money on the technology. My database is free. My programming language, Cascading Style Sheet template, Model-View-Controller framework are all free. I didn't want this project to be an exercise in finding obscure technology, or buying something new. I'm not using anything special, or anything a web developer ten years ago wouldn't recognize.
As I progress, I will update this page with some of the more specific details like the structure of my tables in MySQL, along with the views created to help make them easier to navigate. Additionally, I'll explain my logic for designing graph-specific functions like finding the shortest path between people (how they are related).
I named the table Person. Deciding what columns the Person table should have was a challenge, and I'm still not certain I made all the right choices.
I decided to include Gender, as every person has a gender.
I also decided to include three birth name fields. While a person may have more or fewer than three names, I predict that the majority of my ancestors and relatives will have first, last, and middle names given at birth. While I could have abstracted the names out from the Person table, that would have meant joining the PersonName table back to Person just to get their name. While the abstration may conceptually be more correct, I didn't think it was fundamentally better than just including the names on the same table.
The above is true for the dates I store on the Person table. I observed that every Person has a birth day, and will have a death day as well (even if I don't know those dates). I separated out the day, month, and year into separate columns because not all three are always known. I also included a flag to indicate if the date is approximate (circa).
Just as a person has one birth day and one death day, there is only 1 place a person is born and one place a person dies. So those fields are included on the Person table as well.
I decided to exclude things like baptism dates and marriage dates. While a person would likely have no more than one baptism date, a lot of people have no baptism date at all. And there may be multiple marriage dates. The Person table was designed primarily to store information about a person where that information is singular and popular. This structure allows me to retrieve a good portion of interesting data about a Person with no joins to other tables.
Here's the MySQL table for Person:
| Column Name | Type | Nullable | Note |
|---|---|---|---|
| personId | int | no | primary key, auto incrementing |
| gender | char(1) | no | |
| firstName | varchar(30) | yes | |
| middleName | varchar(30) | yes | |
| lastName | varchar(30) | yes | |
| birthYear | smallint | yes | |
| birthMonth | tinyint | yes | Months will be displayed like JAN, FEB, but they are stored as tinyint |
| birthDay | tinyint | yes | Displayed with leading zero padding, stored as tinyint |
| birthIsCirca | tinyint | no | Acts like a bit. I have had issues using BIT datatypes with PHP and CodeIgniter. Default is zero. |
| birthPlace | varchar(300) | yes | |
| deathYear | smallint | yes | |
| deathMonth | tinyint | yes | Months will be displayed like JAN, FEB, but they are stored as tinyint |
| deathDay | tinyint | yes | Displayed with leading zero padding, stored as tinyint |
| deathIsCirca | tinyint | no | Acts like a bit. I have had issues using BIT datatypes with PHP and CodeIgniter. Default is zero. |
| deathPlace | varchar(300) | yes |
Each person gets one record on this table. This table contains no information about parents or children or spouses.
This table was much more difficult. I went round and round with ways to handle the representation of edges in my relational database.
Here's the main issue: an edge connects exactly two vertices. In graph theory, an edge has direction which means connecting Vertex A to Vertex B does not connect Vertex B back to Vertex A in the same way. It's similar to a one-way street; the edge goes only one way. A very simple way to represent that would be an Edge table that has a fromPersonId column and a toPersonId column. This works fine with regular graph theory, as well as the idea that an Edge connecting two Vertices as "Is a Parent Of" does not work both directions.
But it does mean that, to represent a marriage (an edge without direction), I would need to create two edges: One that connects the Husband to the Wife, and a separate edge that connects the Wife to the Husband. If either edge were missing, the data would show something unreasonable like "Joe is married to Jane, but Jane is not married to Joe." The database has no concept of what a marriage is, so it's fine with that situation. But I'm not. I did not want to rely on two edges to represent a marriage relationship accurately.
I decided that one Edge must be sufficient to represent a marriage. This is for data fidelity which is more important to me than adhering to purity of graph theory.
So if your marriage Edges have direction, and there's only one Edge per marriage, you would have a tough time
discovering all the mariages a person has because they could be either the From or the To person.
You could mitigate this by making sure you search both From and To columns only when looking for spouses.
(it doesn't work if looking for children... you would get parents as well). You could create a view, for example
that selects every Marriage type out of the Edge table twice, the second time swapping the From/To ids. That's
not a bad idea. I did that initially. It looks something like:
create view vwSpouse as
select Edge.fromPersonId as PersonId
, Edge.toPersonId as SpouseId
from Edge
where Edge.relationshipType = 'is_married_to'
union
select Edge.toPersonId as PersonId
, Edge.fromPersonId as SpouseId
from Edge
where Edge.relationshipType = 'is_married_to';
This gets you a simple way to select all the spouses for a person based on their ID. But when discovering how any
two people are related, I can't look at just marriages. I need to look at the is_parent_of relationship
as well. But, like marriages, a person may be a parent (from), or a child (to). And I need to see all the
is_parent_of relationships a person participates in as either parent or child.
You can probably design the next view I would make, based on the vwSpouse view above. But the problem is
that I lose direction. Instead of "SpouseId" in the top query, it would be "ChildId",
which means the second query would be "ParentId" which means... this isn't one view. It's two:
one to show "PersonId is the parent of these People" and a second view to show "PersonId
is the child of these People".
To be fair, the Is the parent of these people isn't a view: it's just selecting from the
Edge table limiting on the is_parent_of relationship type. The direction is already there.
So; with three separate queries (four if you count each of the Union queries in vwSpouse), I can finally discover all the immediate relationships a person has as either spouse, child, or parent. For those playing along at home, this could be a PersonId-OtherPersonId-RelationshipType style union query, which I would probably make into a view for simplcity and consistency. This is just to discover the relationships.
The Edge table will have only 1 PersonId column.
This will make it insanely easy to query the Edges for every single relationship for a person. All those four
queries above turn into this:
select * from Edge where PersonId = PersonId;
Now, there is a trade off/problem. If you saw it already, kudos to you. If not, here it is: each edge connects exactly two Vertices, so with only 1 Person (vertex) on the row, how can I find the second Vertex of the Edge?
The answer is to make each single Edge exactly two rows on the Edge table: one for each Vertex. They can be grouped together by a common "Relationship Key" which will appear on the table exactly twice: once for each Vertex. Direction can be indicated with a "isTarget" flag. For an is_married_to Edge type, both Vertices wil have the isTarget flag set to true (this person is the target of an is_married_to relationship). For an is_parent_of Edge type, only the child will have isTarget set to true.
In the order of fairness, I'll show the real query to find all the relationships for PersonID 1.
In MySQL, it's something along the lines of:
select Edge.*
from Edge
where PersonId <> 1
and RelationshipKey in
(
select RelationshipKey
from Edge
where PersonId = 1
);
Basically; show me all the relationship information for the people that ARE NOT person 1, but share a relationshp key
with person 1.
The next challenge was deciding how to create those relationship keys. MySQL has a nice auto_increment feature which you can use to assign a column to have an ever-increasing integer value. But that creates a unique value per row, and I needed a unique value every two rows.
So I again decided to fudge the graph model a little. I created a separate table for Relationship which will have just one row per relationship. This table will create that ever-increasing integer, as well as tell me the relationship type. The Vertices that participate in that relationship will be in a separate table, sharing the relationship key as a foreign key.
I could have gone a different route. I could identify the relationship TYPE on the Edge table. I could have used a globally-unique identifier (GUID) as a relationship key. That way, it could be created PRIOR to creating the two Edge rows and inserted as the Edge rows were being created. Or, I could read the largest "relationship key" from the Edge table first, and use that-plus-one when inserting the two new Edge rows.
None of the keep-it-pure-graph-model solutions seemed fundamentally better than creating a separate table. At the end of the day, a working model is what matters. What I'm truly trying to build is a way to have a relational database answer questions that are hard for relational databases, but easy for graph databases. To that end, I am mimicking a graph database structure, but not duplicating it. Having this separate table will not hinder me in my quest, and it helps a lot.
So, here are the two tables I use to approximate an Edge:
| Column Name | Type | Nullable | Note |
|---|---|---|---|
| relationshipId | int | no | primary key, auto incrementing |
| relationshipType | varchar(64) | no | Two possible values: :IS_PARENT_OF: and :IS_MARRIED_TO: |
| Column Name | Type | Nullable | Note |
|---|---|---|---|
| personRelationshipId | int | no | primary key, auto incrementing |
| personId | int | no | Foreign Key to Person(personId) |
| relationshipId | int | no | Foreign Key to Relationship(relationshipId) Each value appears on this table exactly twice. |
| isTarget | tinyint | no | Only 1 or 0 instead of a BIT |