I started with this book: Relational Database Design Clearly Explained (The Morgan Kaufmann Series in Data Management Systems) (Paperback) by Jan L. Harrington and found it very clear and helpful and as you get up to speed this one was good too Database Systems: A Practical Approach to Design, Implementation and Management (International Computer Science … Read more
It depends on the operations that occur on the table. If there’s lots of SELECTs and very few changes, index all you like…. these will (potentially) speed the SELECT statements up. If the table is heavily hit by UPDATEs, INSERTs + DELETEs … these will be very slow with lots of indexes since they all … Read more
They’re called indexed views in SQL Server – read these white papers for more background: Creating an Indexed View Improving Performance with SQL Server 2008 Indexed Views Basically, all you need to do is: create a regular view create a clustered index on that view and you’re done! The tricky part is: the view has … Read more
For more international use, one schema to consider is the one used by Drupal Address Field. It’s based on the xNAL standard, and seems to cover most international cases. A bit of digging into that module will reveal some nice pearls for interpreting and validating addresses internationally. It also has a nice set of administrative … Read more
Yes, you can do this. See the MySQL reference (version 5.5). A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index allows multiple … Read more
A clustered index alters the way that the rows are stored. When you create a clustered index on a column (or a number of columns), SQL server sorts the table’s rows by that column(s). It is like a dictionary, where all words are sorted in alphabetical order in the entire book. A non-clustered index, on … Read more
I think the key question to ask here is ‘Who / What is going to be using the history’? If it’s going to be mostly for reporting / human readable history, we’ve implemented this scheme in the past… Create a table called ‘AuditTrail’ or something that has the following fields… [ID] [int] IDENTITY(1,1) NOT NULL, … Read more
It is possible to represent addresses from lots of different countries in a standard set of fields. The basic idea of a named access route (thoroughfare) which the named or numbered buildings are located on is fairly standard, except in China sometimes. Other near universal concepts include: naming the settlement (city/town/village), which can be generically … Read more
It definitely depends on the actual content of your database. If you’re using it to store session information, then by all means wipe it immediately when the session expires (or is closed), you don’t want that garbage lying around. As it cannot really be used again for any practical purposes. Basically, what you need to … Read more
I say it’s a bad idea, generally (with some exceptions, perhaps). First, your database should be backed up regularly, so you should never be in a situation where you would lose data permanently because of a DELETE (unless it’s a deletion of just-added data, of course). Second, a soft delete like this means you now … Read more