StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>My advice on data modeling is:</p> <ul> <li>You should favour optional (nullable) columns over 1:1 joins <em>generally speaking</em>. There are still instances where 1:1 makes sense, usually revolving around subtyping. People tend to be more squeamish when it comes to nullable columns than they do about joins oddly;</li> <li>Don't make a model <em>too</em> indirect unless <em>really</em> justified (more on this below);</li> <li>Favour joins over aggregation. This can vary so it needs to be tested. See <a href="http://www.cforcoding.com/2009/07/oracle-vs-mysql-vs-sql-server.html" rel="nofollow noreferrer">Oracle vs MySQL vs SQL Server: Aggregation vs Joins</a> for an example of this;</li> <li>Joins are better than N+1 selects. An N+1 select is, for example, selecting an order from a database table and then issuing a separate query to get all the line items for that order;</li> <li>The scalability of joins is <em>usually</em> only an issue when you're doing mass selects. If you select a single row and then join that to a few things rarely is this a problem (but sometimes it is);</li> <li>Foreign keys should <em>always</em> be indexed unless you're dealing with a trivially small table;</li> </ul> <p>More in <a href="https://stackoverflow.com/questions/621884/database-development-mistakes-made-by-appdevelopers">Database Development Mistakes Made by AppDevelopers</a>.</p> <p>Now as for directness of a model, let me give you an example. Let's say you're designing a system for authentication and authorization of users. An overengineered solution might look something like this:</p> <ul> <li>Alias (id, username, user_id);</li> <li>User (id, ...);</li> <li>Email (id, user_id, email address);</li> <li>Login (id, user_id, ...)</li> <li>Login Roles (id, login_id, role_id);</li> <li>Role (id, name);</li> <li>Role Privilege (id, role_id, privilege_id);</li> <li>Privilege (id, name).</li> </ul> <p>So you need 6 joins to get from the username entered to the actual privileges. Sure there might be an actual requirement for this but more often than not this kind of system is put in because of the hand-wringing by some developer thinking they might someday need it even though every user only has one alias, user to login is 1:1 and so on. A simpler solution is:</p> <ul> <li>User (id, username, email address, user type)</li> </ul> <p>and, well, that's it. Perhaps if you need a complex role system but it's also quite possible that you don't and if you do it's reasonably easy to slot in (user type becomes a foreign key into a user types or roles table) or it's generally straightforward to map the old to the new.</p> <p>This is thing about complexity: it's easy to add and hard to remove. Usually it's a constant vigil against unintended complexity, which is bad enough without going and making it worse by adding unnecessary complexity.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload