6 ms·

Ever wonder what the Wikipedia database schema looks like?

151 points by makimaki 18y ago

mooneater 18y ago
Lovely. Can anyone comment on what software was used to produce this diagram?
- wallflower 18y 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.
  - mooneater 18y 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_wonder_what_the_wikipedia_database_schema/c075q5a http://www.reddit.com/r/programming/comments/7r3mg/ever_wond...
- Sujan 18y ago
  DBDesigner 4.
  - wenbert 18y 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?
    - jm4 18y ago
      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.
      - wenbert 18y ago
        Thanks. I was hoping to find a Free (as in beer) one... I have been looking for quite awhile already but still could not find anything that is Open Sourced
        delano 18y ago
        You could start an instance of Windows at Amazon to run DBDesigner for a few hours ($0.125 per).
    - Angostura 18y ago
      SQLEditor might be worth a look: http://www.malcolmhardie.com/sqleditor/ 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.
pietro 18y ago
Wow! That's a surprisingly simple schema for an application of Wikipedia's size and scope.
- AndrewDucker 18y ago
  Size and scope don't need complexity - Wikipedia's big, not complex.
  - illume 18y ago
    A diagram of hyperlinks would show it's complexity.
    - pclark 18y ago
      lots of hyperlinks != complex db
      - illumen 18y ago
        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)
        jm4 18y ago
        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.
dan_sim 18y 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.
- CalmQuiet 18y 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 18y ago
    I'm not sure, but at a guess it would probably be either Lee Daniel Crocker or Brion Vibber.
  - vulpes 18y ago
    I think this guy had a lot to do with it: http://dammit.lt/domas-mituzas/ http://dammit.lt/domas-mituzas/ A lot of interesting info can be found here: http://dammit.lt/uc/workbook2007.pdf http://dammit.lt/uc/workbook2007.pdf
    - CalmQuiet 18y ago
      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.
- dhoe 18y 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".
yan 18y 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_sim 18y 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 18y ago
    Most wiki software caches rendered pages in this case.
- bprater 18y 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 18y ago
That's rad!
dpeq 18y ago
Why do they refer to the table name in every fieldname?
- ponnap 18y 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.
  - Ezra 18y 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 18y 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.
Angostura 18y 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?
- Ezra 18y 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.
  - Angostura 18y ago
    Head-slap "of course" Thanks for taking the time Ezra.
foobar2k 18y ago
No

151 points by makimaki 18y ago

mooneater 18y ago

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

wallflower 18y 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.
- mooneater 18y 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_wonder_what_the_wikipedia_database_schema/c075q5a http://www.reddit.com/r/programming/comments/7r3mg/ever_wond...
Sujan 18y ago
DBDesigner 4.
- wenbert 18y 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?
  - jm4 18y ago
    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.
    - wenbert 18y ago
      Thanks. I was hoping to find a Free (as in beer) one... I have been looking for quite awhile already but still could not find anything that is Open Sourced
      - delano 18y ago
        You could start an instance of Windows at Amazon to run DBDesigner for a few hours ($0.125 per).
  - Angostura 18y ago
    SQLEditor might be worth a look: http://www.malcolmhardie.com/sqleditor/ 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.

pietro 18y ago

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

AndrewDucker 18y ago
Size and scope don't need complexity - Wikipedia's big, not complex.
- illume 18y ago
  A diagram of hyperlinks would show it's complexity.
  - pclark 18y ago
    lots of hyperlinks != complex db
    - illumen 18y ago
      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)
      - jm4 18y ago
        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.

dan_sim 18y 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.

CalmQuiet 18y 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 18y ago
  I'm not sure, but at a guess it would probably be either Lee Daniel Crocker or Brion Vibber.
- vulpes 18y ago
  I think this guy had a lot to do with it: http://dammit.lt/domas-mituzas/ http://dammit.lt/domas-mituzas/ A lot of interesting info can be found here: http://dammit.lt/uc/workbook2007.pdf http://dammit.lt/uc/workbook2007.pdf
  - CalmQuiet 18y ago
    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.
dhoe 18y 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".

yan 18y 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_sim 18y 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 18y ago
  Most wiki software caches rendered pages in this case.
bprater 18y 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 18y ago

That's rad!

dpeq 18y ago

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

ponnap 18y 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.
- Ezra 18y 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 18y 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.

Angostura 18y 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?

Ezra 18y 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.
- Angostura 18y ago
  Head-slap "of course" Thanks for taking the time Ezra.

foobar2k 18y ago