The next big paradigm shift in database design

Today I came across what I predict will be the next big paradigm shift in the world of database design. This brilliant work I stumbled upon has been created by 4 students at the Koninklijk Atheneum of Sint-Niklaas, an innovative institute devoted to developing bleeding edge technologies. It is a completely new way of structuring MySQL database tables, the next epoch in design if you will.

Before we have a look at the code, let’s first consider the strong points of this new way of structuring database tables.

Benefits:

• It does away with all the confusing field type’s – just use INT’s for ID’s, and text for all the rest.
• Create only one table, which then holds all your data. This can be done by creating fields such as peopleWith1pc, peopleWith2pcs, peopleWith3pcs, ect. Brilliant no?
• To make it even more easy, it also does away with any sort of naming convention, after all, it’s only confusing!

Let’s have a look at a simple example where the new pattern is applied to a database in which survey results are stored. The table stores ‘answers’ of people who took the survey. The field names are in Dutch, but this should not prevent you from deducing the overall awesomeness.

Code:

[cc lang=”sql” width=”600″]
CREATE TABLE IF NOT EXISTS antwoorden (
id int(11) NOT NULL auto_increment,
geslacht text NOT NULL,
studeren text NOT NULL,
geboorteJaar int(4) NOT NULL default ‘0’,
gsm text NOT NULL,
prijsGsmToestel text,
herlaadmanier text,
provider text,
bedragPerMaan text,
smsPerDag text,
belPerDag text,
mp3 text NOT NULL,
prijsMp3 text,
gebruikMp3 text,
pc text NOT NULL,
aantalPc1 text,
aantalPc2 text,
aantalPc3 text,
aantalPc4 text,
aantalPc5 text,
aantalPc6 text,
aantalPc7 text,
aantalPc8 text,
aantalPc9 text,
aantalPc10 text,
prijsPc text,
pcWerk text,
pcSchool text,
pcGamen text,
pcFilm text,
pcComm text,
pcSociaal text,
pcMuziek text,
pcAndere text,
pcGebruik text,
pcProvider text,
pc2000 text,
pcXp text,
pcVista text,
pc7 text,
pcLeo text,
pcSnowLeo text,
pcLinux text,
pcUnix text,
pcAndereBest text,
pda text NOT NULL,
prijsPda text,
pdaWerk text,
pdaSchool text,
pdaGamen text,
pdaAndere text,
pdaGebruik text,
pdaProvider text,
tv text NOT NULL,
tvGebruik text,
tvProvider text,
gamen text NOT NULL,
play1 text,
play2 text,
play3 text,
xbox text,
xbox360 text,
psp text,
pspGo text,
gameCube text,
wii text,
ds text,
gameAndere text,
consoleAantal text,
prijsGames text,
digiFoto text,
filmCamera text,
extHD text,
memStick text,
videoRec text,
dvdSpelRec text,
bluRay text,
dlToetsMuis text,
gameToeb text,
surround text,
printer text,
webcam text,
scanner text,
enqueteLeuk text NOT NULL,
PRIMARY KEY (id)
)
[/cc]

References:

• Official paper on TCP
• A deeper look at the benefits
• Step-by-step tutorial