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.
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
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)
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.
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.
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.
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.
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.
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.
> "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.
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.
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.
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.
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.
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.
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.
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'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.
46 comments
[ 3.9 ms ] story [ 85.0 ms ] threadhttp://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.
>>> 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)
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 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 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!
A lot of interesting info can be found here: http://dammit.lt/uc/workbook2007.pdf
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 @_@
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.
We call it "the startup".
MediaWiki is an open source project that has had thousands of man-hours poured into it.
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 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.
After a query you can also easily identify which table the field came from.
That's my guess at least.
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.
Thanks for taking the time Ezra.