If only to avoid wasting semantic details about data inside a table. How do i save these details in MySQL, such will be able to access data as well as look for the articles while using semantic data.

For instance, I've got a article about Apple and Microsoft. The semantic data is going to be like
Person : Jobs
Person : Steve Ballmer
Company : Apple
Company : Microsoft

I wish to save the data without losing the data that Jobs and Steve Ballmer are persons and Apple and Microsoft are companies. I should also look for articles about Jobs / Apple.

Person and Company aren't the sole possible types, hence adding new fields isn't viable. Since the kind of the information will be saved, I am unable to use FullText area type directly.

Update - They are two options that i'm thinking about.

  1. Save the information inside a full text column as serialized php array.
  2. Create another table with 3 posts


| id | subject | object        |
|  1 | Person  | Steve Ballmer |
|  1 | Person  | Steve Jobs    |
|  1 | Company | Microsoft     |
|  1 | Company | Apple         |
|  2 | Person  | Obama         |
|  2 | Country | US            |

You are focusing on a tough and fascinating problem! You might get some interesting ideas from searching in the Dublin Core Metadata Initiative.


To really make it simple, think about your metadata products as all gelling one table.


Ballmer employed-by Microsoft
Ballmer is-a Person
Microsoft is-a Organization
Microsoft run-by Ballmer
SoftImage acquired-by Microsoft
SoftImage is-a Organization
Joel Spolsky is-a Person
Joel Spolsky formerly-employed-by Microsoft
Spolsky, Joel dreamed-up StackOverflow
StackOverflow is-a Website
Socrates is-a Person
Socrates died-on (some date)

The secret here's that some, although not all, the first and third column values have to be BOTH arbitrary text And function indexes into the foremost and third posts. Then, if you are trying to puzzle out what your computer data base is wearing Spolsky, you are able to full-text search the first and third posts for his title. You will get out a lot of triplets. The values you discover will explain a great deal. If you wish to learn more, searching again.

To drag this off you'll most likely must have five posts, the following:

Full text subject  (whatever your user puts in)
Canonical subject (what your user puts in, massaged into a standard form)
Relation (is-a etc)
Full text object
Canonical object

The purpose of the canonical types of your subject and object would be to allow queries such as this to operate, even when your user puts in "Joel Spolsky" and "Spolsky, Joel" in 2 different places even when they mean exactly the same person.

  FROM relationships a
  JOIN relationships b (ON a.canonical_object = b.canonical_subject)
 WHERE MATCH (subject,object) AGAINST ('Spolsky')

You might like to normalize your computer data table by looking into making 2 tables.

| id | subject |
|  1 | Person  |  
|  2 | Company |
|  3 | Country |

| id | subject-id | object        |
|  1 |          1 | Steve Ballmer |
|  2 |          1 | Steve Jobs    |
|  3 |          2 | Microsoft     |
|  4 |          2 | Apple         |
|  5 |          1 | Obama         |
|  6 |          3 | US            |

This enables you to definitely easier see all of the different subject types you've defined.