Sunday, September 11. 2005NermalisationComments
Display comments as
(Linear | Threaded)
I normally use fixed char(36) primary keys containing an UUID. If collation is binary this is not much slower than int (of course it is since it is larger), but it has so many advantages. You get a unique id for the lifetime of the record (including exports, backups and syncronisation). This is good for auditing and merging of data streams.
While this extends beyond the scope of an introduction, it might be as good a time as any to draw everyone's attention to the *auto_increment_increment* and *auto_increment_offset* server-options (both added in 5.0.2). These might be relevant depending on one's use case.
http://dev.mysql.com/doc/mysql/en/server-system-variables.html
Nice little article, and I like how you've used cats in your examples :)
I am a little unclear what the point here is though... break tables up?
The point is that you have the option to not do, but then you'll be in a world of pain. The point is also to show that if you do it, how to do it. A more technical approach is here:
http://en.wikipedia.org/wiki/Database_normalization
I can't help but miss one table, or at least one column. Because the last two arrangements don't state WHICH cat lives with which owner under which name.
@Max Sinister: Yes, there are many possible problems that could arise from this. The tables should be: #catID; #cat_nameID, name, .catID; #ownerID; #locationID, location; #arrangementID, .catID, .ownerID, .locationID, from, until. The other good thing about having locationID as its own table is that it doesn't turn on the owner. A cat can have its own residence (i.e., Stray). Owners can change residences, and the 'lives with' would be implicated by the catIDxownerIDxlocationID. You can just access it via your scripting language: "Lives with" + SELECT catIDxownerIDxlocationID;.
How do I auto-update the dates on changes? Is it best to use a view for the final table, or can we set up a dependency in a better way? I've seen that this can be very complicated. Also, wouldn't it be better to use a tighter grain for the dates? We may as well because there's a time stamp option.... Finally, don't you create a primary key in the lives_with table for the contingency that the cat has two owners (for example, if divorced parents send their child's cat back and forth between houses with the child).
|
QuicksearchComments
about Wed, 31.10.2012 13:46
My friendly Offtopic Channel
in IRC has horror stories
about this, it is Halloween,
after all.
"Well, I do
have [...]
about Mon, 15.10.2012 09:41
What if we use SSDs. Can we
then assume memory limits
are not as significant
because SSDs can handle
random access [...]
about Fri, 05.10.2012 07:46
In fact, I am pretty certain
that this also breaks
mysql_upgrade or has the
potential to do so.
about Fri, 05.10.2012 06:44
It is quite a bit worse than
that: Right now, the default
RPM as distributed by Oracle
will fail to install if it
[...]
about Thu, 04.10.2012 18:52
> MyISAM in mysql.* has to go
before GTID can be
sucessfully deployed.
I
think you meant to say
before it can b [...]
about Thu, 04.10.2012 18:36
Sorry, it wasn't clear given
that none of this is
actually mentioned.
about Thu, 04.10.2012 13:47
You can turn it on with FLUSH
PRIVILEGES once the tables
exist.
about Thu, 04.10.2012 11:34
I think I have to install a
comment voting plugin just
so that i can +1 what Justin
just wrote.
Categories
Blog AdministrationDisclaimerAll examples, code-snippets, advice, trivia, etc. are supplied in the hope that they are useful, but no warranty of any kind is given. Use of trademarks is not intended as a challenge to them.
ImpressumCopyright 2005-2012 bei den jeweiligen Autorinnen der Beiträge. Die Infrastruktur von MySQL-dump wird betrieben von:
Azundris, Immanuelkirchstraße 18, 10405 Berlin, Germany
|
Eine Einführung in die Normalisierung von Datenbanken, die kein Vorwissen voraussetzt und Katzencontent liefert. ASCII version, English versionStark vereinfacht geht es bei Normalisierung um Flexibilität und Redundanzvermeidung. Gegeben seien Katzen u
Tracked: Sep 11, 22:21
Relational Database Management Systems (RDBMSs) use special forms to keep data from being kept redundant: normalized tables. If you’re like me, you haven’t memorized or completely understood normalization up to now. If you do, however, wan...
Tracked: Nov 28, 14:35
In Five Common PHP database problems, Jack Herrington writes about five common database problems that occur in PHP applications -- including database schema design, database access, and the business logic code that uses the database -- as well as their s
Tracked: Aug 11, 10:40