Modeling Atomic Facts in a Relational Database
I want to record what different sources have to say about the historical figure. those.
- The Wikipedia website states that Susan B. Anthony was born on February 15, 1820, and her favorite color was blue.
- The book Century of Struggle states that Susan B. Anthony was born on February 12, 1820, and her favorite color was red.
- The History of Women Suffrage states that Susan B. Anthony was born on February 15, 1820, and her favorite color was red, and she was Abraham Lincoln's second cousin.
I also want researchers to be able to express their confidence, for example, with a percentage, in the individual statements these sources make. i.e.
- User A is 90% certain that Susan B. Anthony was born on February 15, 1820; 75% are sure that her favorite color was blue and 30% are sure that she was the second cousin with Abraham Lincoln.
- User B 30% certain that Susan B. Anthony was born on February 12, 1820; 60% are sure her favorite color was blue, and 10% are confident that she was a second cousin with Abraham Lincoln.
Next, I want every user to have an idea of Susan B. Anthony showing her birthday, favorite color, and the relationship that users think is likely to be true.
I also want to use a relational data store, and the way I can do this is to create a separate table for each distinct type of atomic fact that I want users to express their confidence. So there would be a total of eight tables for this example and three separate tables for three separate atomic facts.
Source(id)
Person(id)
Claim(claim_id, source, FOREIGN KEY(source) REFERENCES Source(id) )
Alleged_birth_date(claim_id, person, birth_date, FOREIGN KEY(claim_id) REFERENCES Claim(id), FOREIGN KEY(person) REFERENCES person(id))
Alleged_favorite_color(claim_id, person, color, FOREIGN KEY(claim_id) REFERENCES Claim(id), FOREIGN KEY(person) REFERENCES person(id))
Alleged_kinship(claim_id, person, relationship type, kin, FOREIGN KEY(claim_id) REFERENCES Claim(id), FOREIGN KEY(person) REFERENCES Person(id))
User(id)
Confidence_in_claim(user, claim, confidence, FOREIGN KEY(user) REFERENCES User(id), FOREIGN KEY(claim) REFERENCES claim(id))
It looks like it gets complicated very quickly, as it actually wants to write down many types of atomic facts. Are there any better ways to do this?
This is, I think, the same problem that Martin Fowler calls Contradictory Observations .
a source to share
RDF is great for this. It is usually described as a metadata format; but in fact it is a graph model of "statements" on triplets.
The whole idea of a "semantic web" is to publish a lot of facts about RDF, and search engines will be inference engines that traverse a single graph to find relationships.
There are also some mechanisms for referencing a triplet, so you can say something about the statement, like the origin (who says this?) Or when it was stated (when did he say it?) Or how much do you believe it to be true , etc.
As a great example, the entire OpenCyc "common sense knowledge base" is queried in RDF
a source to share
You should try the Star Schema model targeting the "Fact" table and several "Dimension" tables. This is a well-studied model and there are many database optimizations for it.
Claim_fact (source_id, person_id, user_id, details_id, weight)
Source_dimension (id, name)
Person_dimension (id, name)
User_dimension (id, name)
details_dimension (id, name NOT NULL, color NULLABLE, affinity NULLABLE, birthday NULLABLE)
Each statement has a source, person, user and details. NAME values for parts will be values such as "relationship", "birthday".
Keep in mind that this is an OLAP schema (not an OLTP framework) and therefore is not fully normalized. The benefits of this outweigh any problems that can be caused by redundancy, as queries against asterisk schemes are highly optimized with DBMS configured to store data.
RECOMMENDED READ: Data Warehouse Toolkit (Kimball et al.)
a source to share
I think you want to use a property bag. Instead of modeling every single type of fact that you want to describe, you want a table that contains an identifier, a "key" (in this case, inferred information (for example, "relationship")), and "value" (in this case, an inferred value ( for example, "Abraham Lincoln")). Then you want to have a second table that links your applicants to that table, as well as the level of confidence they have in this information. just enter the source id, property id and make sure the source has the information. Thus, you can have a source that contains either a lot or a little information,you can also simulate different sources with different levels of confidence in a given attribute, and there is no limit to the number of different types of information that you can store.
This is a pretty standard solution for situations like yours where you have a lot of additional information that you want to cross-reference.
a source to share
It looks like it gets complicated very quickly.
You're not kidding. Look at the work of ontology and knowledge representation .
a source to share