Ever wonder what the Wikipedia database schema looks like?(upload.wikimedia.org)
upload.wikimedia.org
Ever wonder what the Wikipedia database schema looks like?
http://upload.wikimedia.org/wikipedia/commons/4/41/Mediawiki-database-schema.png?
8 comments
I'm amazed how simple it is. I worked on corporate apps that have much more complex database schema. I think there's something to learn about the one from wikipedia : well designed ideas create well designed databases.
Me too. I installed SAP the other day - it doesn't even install if you don't have 20 GB free disk space, and it creates something in the order of several ten thousand tables. There ought to be job titles like "Corporate Simplificator".
Its crazy, you would think this is horribly inefficient. I worked a bit with Oracle Siebel, its a customer relations management software and the company decided to use it for its system. The database is just this giant juggernaut with thousands of tables generated automatically, if you look closely there is redundancy everywhere, which I assume is for performance enhancement, but even then it is horribly slow.
Its absolutely drives you nuts because everything, from business information, to server configuration, and even even the interface - menu items, buttons, page layout, links, is stored in the database. Now this may make the software less 'bug free' and thus make less trouble for Oracle, but it is an absolute nightmare for the developer because everything has to be changed through the IDE, which is basically a retarded version of a database browser without the ability to run SQL update command, and only allows you to change one row at a time, you might as well as be a monkey. Working with the database directly is apparently against the rules of Oracle and will revoke the support from Oracle.
Good thing I stopped working there @_@
Its absolutely drives you nuts because everything, from business information, to server configuration, and even even the interface - menu items, buttons, page layout, links, is stored in the database. Now this may make the software less 'bug free' and thus make less trouble for Oracle, but it is an absolute nightmare for the developer because everything has to be changed through the IDE, which is basically a retarded version of a database browser without the ability to run SQL update command, and only allows you to change one row at a time, you might as well as be a monkey. Working with the database directly is apparently against the rules of Oracle and will revoke the support from Oracle.
Good thing I stopped working there @_@
> "Its absolutely drives you nuts because everything, from business information, to server configuration, and even even the interface - menu items, buttons, page layout, links, is stored in the database."
There's a good reason for this. At any big company, it's much easier to change a value in a database table than it is to make a code change.
There's a good reason for this. At any big company, it's much easier to change a value in a database table than it is to make a code change.
Configuration items are not hard coded ... they are stored in configuration files (e.g. app.config, settings.ini, app.xml etc.)
> "Corporate Simplificator"
We call it "the startup".
We call it "the startup".
Yes it is nice to have a well designed database. However, it is part of the nature of business to be full contradictions and exceptions. That is reflected in their data models and applications.
> However, it is part of the nature of business to be full contradictions and exceptions.
I agree with you on this point, and so I voted you up. I disagree with the implication, though. There are two possibilities that come to mind when people encounter contradictions and exceptions in a system:
1. There is no unifying model. The exceptions are truly exceptional, and have to be dealt with as their own case apart from the main logic and rules of the system.
2. The existence of a unifying model is unknown, because no one has thought to look for it yet. Additional cases that need to be resolved are assumed to be exceptions unless it is blatantly obvious they are not, and are immediately developed as such.
Experiences I and others have had suggest that the latter case is far more likely. So I agree that the nature of business as practices is full of exceptions and contradictions, but I disagree that being so is inevitable.
I agree with you on this point, and so I voted you up. I disagree with the implication, though. There are two possibilities that come to mind when people encounter contradictions and exceptions in a system:
1. There is no unifying model. The exceptions are truly exceptional, and have to be dealt with as their own case apart from the main logic and rules of the system.
2. The existence of a unifying model is unknown, because no one has thought to look for it yet. Additional cases that need to be resolved are assumed to be exceptions unless it is blatantly obvious they are not, and are immediately developed as such.
Experiences I and others have had suggest that the latter case is far more likely. So I agree that the nature of business as practices is full of exceptions and contradictions, but I disagree that being so is inevitable.
I'll second that. I always like pouring over schemas that have been tested by heavy use - and wikimedia sure has.
The only thing that would make the png more valuable would be some accompanying discussion: I'd like to know
who was/were mostly responsible for the design. Seems like I had searched through WP itself one time but found surprisingly little.
what refactoring lessons they encountered during the history of the project.
Much good karma to someone who can get proper credit to the database team!
The only thing that would make the png more valuable would be some accompanying discussion: I'd like to know
who was/were mostly responsible for the design. Seems like I had searched through WP itself one time but found surprisingly little.
what refactoring lessons they encountered during the history of the project.
Much good karma to someone who can get proper credit to the database team!
I think this guy had a lot to do with it: http://dammit.lt/domas-mituzas/
A lot of interesting info can be found here: http://dammit.lt/uc/workbook2007.pdf
A lot of interesting info can be found here: http://dammit.lt/uc/workbook2007.pdf
Vulpes, thanks: that .pdf of a 2007 presentation by Domas Mituzas at MySQL Users Conference just about has it all: Cache strategies; performance specifics; application components; DB balancing; storage strategies; use of compression; load balancing; administration; the People involved.
Certainly gives any LAMP-based development team a list of resources that have come together to be useful for such a project.
I'm not sure, but at a guess it would probably be either Lee Daniel Crocker or Brion Vibber.
It's quite neat.. Although, they are really only tracking three key entities - Users, Pages and Media. You'd hope it would be pretty simple.
It's amazing what happens when good designers think about their data structures, rather than write first and refactor later (or don't).
I doubt MediaWiki 0.001 beta had this exact database schema.
MediaWiki is an open source project that has had thousands of man-hours poured into it.
MediaWiki is an open source project that has had thousands of man-hours poured into it.
I realize that this isn't the same schema that launched MediaWiki. Its retained focus and ability to sustain growth without sprawl tells me that the schema was well thought out.
Question to those who assumed the schema would be more complicated: Why? Maybe I'm not as good with DB design as I should be, but what parts of Wikipedia did you assume would garner more complexity? Their data layout is pretty flat and I'd actually be surprised if it was more complex than this diagram.
Often, simple growth in a company will create a messy schema. Version 1 is often clean, but as you iterate and more functionality is added, the schema often undergoes a hideous transformation from museum-piece to something not that.
Lovely. Can anyone comment on what software was used to produce this diagram?
It looks similar to what we get from our $$ Erwin modeler but not exactly. Erwin can do quite a bit of reverse-engineering or read a SQL file.
Turns out this is another cross-post from reddit. They are discussing how this was made:
http://www.reddit.com/r/programming/comments/7r3mg/ever_wond...
DBDesigner 4.
I just Googled this. Looks awesome but no support for Mac :-(. Do you know one that is as good as DB Designer that runs on a Mac?
You could take a look at Aqua Data Studio or DB Visualizer. I haven't used DB Designer so I don't know how they compare, but DB Visualizer is a pretty nice tool and not too expensive.
SQLEditor might be worth a look:
http://www.malcolmhardie.com/sqleditor/
Costs $70-something I think, but has a free trial and generates the SQL to set up the tables from your diagrams.
http://www.malcolmhardie.com/sqleditor/
Costs $70-something I think, but has a free trial and generates the SQL to set up the tables from your diagrams.
No
I'm not a database specialist, so I hope you will allow me to ask a daft question. Why, in the user table is the password held as a TinyBLOB. To allow for Unicode, or somesuch?
In this case, the password field doesn't hold just a password, or a rot13 of a password, or even a digest of a password.
user_password is a concatenated md5 hash of user_id, a hyphen (-), and md5 hash of current password. ie. MD5(CONCAT(user_id, "-", MD5("PASSWORD")))
The reason for this is security, obviously.
The tinyblob then, is used for convenience/efficiency; it's a good way to store a hash. They might also do some tricks on the data (ie. convert to base 64 before it's stored, or something like that), but that's the general idea.
user_password is a concatenated md5 hash of user_id, a hyphen (-), and md5 hash of current password. ie. MD5(CONCAT(user_id, "-", MD5("PASSWORD")))
The reason for this is security, obviously.
The tinyblob then, is used for convenience/efficiency; it's a good way to store a hash. They might also do some tricks on the data (ie. convert to base 64 before it's stored, or something like that), but that's the general idea.
Head-slap "of course"
Thanks for taking the time Ezra.
Thanks for taking the time Ezra.
Wow! That's a surprisingly simple schema for an application of Wikipedia's size and scope.
Size and scope don't need complexity - Wikipedia's big, not complex.
A diagram of hyperlinks would show it's complexity.
lots of hyperlinks != complex db
It's a different kind of database. If you think wikipedia is not a complex database then you're sorely mistaken. The SQL schema may not be complex, but everyone knows that wikipedia is made from a wiki -- a different type of database.
>>> issubclass(wiki, database)
True
>>>
"""Ward Cunningham, the developer of the first wiki software, WikiWikiWeb, originally described it as "the simplest online database that could possibly work"."""
The SQL is just the datastore. The wiki is the real database. Now to show that the wiki is a complex database, you would show a diagram of all the hyperlinks between pages... and the many other ways the data is linked (categories, tags etc)
>>> issubclass(wiki, database)
True
>>>
"""Ward Cunningham, the developer of the first wiki software, WikiWikiWeb, originally described it as "the simplest online database that could possibly work"."""
The SQL is just the datastore. The wiki is the real database. Now to show that the wiki is a complex database, you would show a diagram of all the hyperlinks between pages... and the many other ways the data is linked (categories, tags etc)
What does that have to do with an SQL database schema? And what's the significance of the link structure? It's not so dissimilar from other websites or the web itself. Sure, it would probably make for a cool looking graph, but it has no bearing on the underlying storage method in this particular case. If it was necessary to understand or perform some kind of analysis on the relationships between objects like social networks and search engines do, they would probably have a more interesting storage mechanism to look at.
Personally, what I find surprising about this whole thing is that people are so amazed at the simplicity of the schema. A wiki is a fairly simple application. Think about it. If you were going to build one it's not much more than a basic CRUD application. You really only have articles (with versioning), user accounts, images/media and whatever other oddball features you want to have like statistics and IP restrictions. Hell, I remember seeing some beginning Ruby on Rails book that used a wiki as the tutorial application. This is pretty basic stuff.
Personally, what I find surprising about this whole thing is that people are so amazed at the simplicity of the schema. A wiki is a fairly simple application. Think about it. If you were going to build one it's not much more than a basic CRUD application. You really only have articles (with versioning), user accounts, images/media and whatever other oddball features you want to have like statistics and IP restrictions. Hell, I remember seeing some beginning Ruby on Rails book that used a wiki as the tutorial application. This is pretty basic stuff.
The wiki is the database, built on top of another database. It uses link structure and other document attributes to form different relations.
The relations(links) are like the relations in an SQL database. The relations in the wikitext are like database relations.
Much of the logic is built into the document attributes(wikitext), coded in the php layer. Mediawiki is a very big, and complex database. 1.5 million LOC. Which is fairly small compared to other databases.
Anyway... I guess my point is that mediawiki is a database, and that it's relations are not in the SQL - but in the wikitext.
see you!
The relations(links) are like the relations in an SQL database. The relations in the wikitext are like database relations.
Much of the logic is built into the document attributes(wikitext), coded in the php layer. Mediawiki is a very big, and complex database. 1.5 million LOC. Which is fairly small compared to other databases.
Anyway... I guess my point is that mediawiki is a database, and that it's relations are not in the SQL - but in the wikitext.
see you!
I understand the part where you said a wiki is a database. I guess it could fit some loose definition of a database. But what does that have to do with anything? The whole point of the article was the simplicity of the SQL schema. The link structure of a wiki has nothing to do with it.
Why do they refer to the table name in every fieldname?
May be it exists in their coding guidelines? Although tablename.tablename_columnname sounds repetitive and verbose, it might in some cases be of help when one deals with too many tables and too many keys with the same name across tables.
>May be it exists in their coding guidelines?
I think the question would be why would this exist in their coding guidelines.
>Although tablename.tablename_columnname sounds repetitive and verbose, it might in some cases be of help when one deals with too many tables and too many keys with the same name across tables.
tablename.tablename_columnname is not likely what's being done for their queries... in many RDBMS's, you can omit the "tablename." part if columnname is unique in the DB, so it will actually reduce repitition and verbosity, even if you hate typing "_" as much as I do.
It also makes dealing with "duplicate" names easier, because there will be no dupes: you won't have any columns or keys across tables that have the same name, because the prefix will differ, and in your queries and various etceras you won't ever have to think "which 'from' is this?"
With all of this, it also eliminates the need for (other, explicit) aliases, if you're doing it right.
Or at least that's the story: some people consider this equivalent to using Hungarian notation in Python ... I'm not one of those people, for the reasons above.
I think the question would be why would this exist in their coding guidelines.
>Although tablename.tablename_columnname sounds repetitive and verbose, it might in some cases be of help when one deals with too many tables and too many keys with the same name across tables.
tablename.tablename_columnname is not likely what's being done for their queries... in many RDBMS's, you can omit the "tablename." part if columnname is unique in the DB, so it will actually reduce repitition and verbosity, even if you hate typing "_" as much as I do.
It also makes dealing with "duplicate" names easier, because there will be no dupes: you won't have any columns or keys across tables that have the same name, because the prefix will differ, and in your queries and various etceras you won't ever have to think "which 'from' is this?"
With all of this, it also eliminates the need for (other, explicit) aliases, if you're doing it right.
Or at least that's the story: some people consider this equivalent to using Hungarian notation in Python ... I'm not one of those people, for the reasons above.
It can make many joins easier since you avoid field conflicts. This way you do not need to include any 'AS' statements in your SQL query.
After a query you can also easily identify which table the field came from.
That's my guess at least.
After a query you can also easily identify which table the field came from.
That's my guess at least.
That's rad!