A very long time ago I required over upkeep of an internet based cms which had quite a clever database table inside it.
The table structure was like:
TABLE: tPage (PK on PageID, Version, Status)
PageID, PageTitle, Version, Status 1, Homepage, 1, Archived 1, Homepage, 2, Live 1, New Home, 3, Draft
TABLE: tElements (PK on PageID, Version, ElementName)
PageID, Version, ElementName, ElementValue (nText) 1, 1, IntroText, Hi, this is our first version. 1, 2, IntroText, This is the current version, 1, 2, MainBody, We sell sprockets. 1, 3, IntroText, Welcome text here 1, 3, MainBody, We sell sprockets and widgets.
To obtain the primary body text from the current home page you'd write a question like:
SELECT ElementValue FROM tElements, tPage WHERE PageID = 100 ElementName = 'MainBody' AND tPage.PageID = tElements.PageID AND tPage.Version = tElements.Version AND tPage.Status = 'Live'
We sell sprockets.
The good thing about this table structure, (in my experience a minimum of) was which i did not have to add new posts to some table after i added new kinds of data. I simply produced a brand new ElementName. But functionally it had been just like a new column.
I have since used this arrangement inside a couple of other projects where I desired a database that will change easily as additional featuresOrcontent material types could be added. For instance, during my above Content management systems example, we began with only IntroText, and MainBody, but with a couple of years later we wound up with CoursePrice, ContactPerson, RelatedCourses, SetupFee, and that i found by doing this of arranging our data to be really flexible.
My real question is, what's this kind of table structure known as? I am not necessarily sure what key phrases I ought to be searching to learn more.
It's known as "entity-attribute-value" or "EAV".