Ever wonder what the Wikipedia database schema looks like?

http://upload.wikimedia.org/wikipedia/commons/4/41/Mediawiki-database-schema.png?

by makimaki • 18 years ago

151 46 18 years ago

upload.wikimedia.org

mooneater2• 18 years ago

Lovely. Can anyone comment on what software was used to produce this diagram?

wallflower• 18 years ago

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.

mooneater2• 18 years ago

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...

Sujan• 18 years ago

DBDesigner 4.

wenbert2• 18 years ago

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?

pietro• 18 years ago

Wow! That's a surprisingly simple schema for an application of Wikipedia's size and scope.

AndrewDucker• 18 years ago

Size and scope don't need complexity - Wikipedia's big, not complex.

illume• 18 years ago

A diagram of hyperlinks would show it's complexity.

dan_sim2• 18 years ago

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.

CalmQuiet2• 18 years ago

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!

ig1• 18 years ago

I'm not sure, but at a guess it would probably be either Lee Daniel Crocker or Brion Vibber.

vulpes• 18 years ago

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

dhoe• 18 years ago

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".

light3• 18 years ago

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 @_@

Andys• 18 years ago

> "Corporate Simplificator"

We call it "the startup".

gamache2• 18 years ago

It's amazing what happens when good designers think about their data structures, rather than write first and refactor later (or don't).

jrockway• 18 years ago

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.

huherto• 18 years ago

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.

azanar• 18 years ago

> 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.

jwilliams• 18 years ago

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.

yan• 18 years ago

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.

dan_sim2• 18 years ago

I personally expected Wikipedia to have an uglier schema because of hacks they would have to do for performance issues. I expected a lot more denormalization and duplications.

graywh• 18 years ago

Most wiki software caches rendered pages in this case.

bprater• 18 years ago

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.

jogaun• 18 years ago

That's rad!

dpeq• 18 years ago

Why do they refer to the table name in every fieldname?

ponnap• 18 years ago

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.

Ezra2• 18 years ago

>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.

dangoldin• 18 years ago

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.

Angostura3• 18 years ago

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?

Ezra2• 18 years ago

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.

Angostura3• 18 years ago

Head-slap "of course"

Thanks for taking the time Ezra.

foobar2k• 18 years ago

Ever wonder what the Wikipedia database schema looks like?

Related Stories