begin quoting Levi Smith as of Wed, Aug 17, 2005 at 11:58:41AM -0400: > Well, I have innodb support on my own box that I'm working on to start with, > but I went and checked my web host and they don't support it. > > However, I'm still looking for an answer, and it seems like maybe I don't > really need "foreign keys and consistency"?
The answer to THAT question depends entirely upon the level of risk you're willing to accept. You don't _need_ to declare foreign keys, you can just have columns of the type of the primary key in the other table used for storing keys you're referring to. > I'm just trying to set up a database the "correct" way, which after much > research on data modelling and 1NF, 2NF, 3NF and whatnot, I came up with > tables called Item, Referrer, Vendor, Buyer, and Categories. But when I > started to try to get these foreign keys and such set up I realized I was > going to have an issue. Item (itm) pk as int description as varchar . . . Category (cat) pk as int pkref as int category as varchar To get all items in the OLD category: SELECT itm.pk, itm.description FROM itm, cat WHERE cat.category = 'OLD' AND itm.pk = cat.pkref To get a list of all categories: SELECT distinct category FROM cat Etc. etc. You don't _need_ foreign keys to make such references. You might have a consistency issue in that if you remove a row from the Item table, you'll have stale data in the Category table, but that won't _hurt_ anything until you start reusing primary keys. And technically, you can write some code to go through and enforce consistency. This will turn most DBAs purple, but then, those are the sorts of DBAs who want you to hire 'em to manage your database for you, and are offended that you might want a database to do no more than hold data. Like most things, features should be used in moderation. There's nothing quite so annoying as being confronted with a ten-thousand-table database, fully normalized, replete with tie-tables, loaded with foriegn keys, bursting with triggers, and dripping with stored procedures... and realizing that all you want to do is to extract a little data, but that to recreate it in a small scale (sans the million-record tables) is a prohibitive task. -Stewart "Solve your problem with your tools at hand, improve it later" Stremler
pgpyonPAyYiFg.pgp
Description: PGP signature
-- [email protected] http://www.kernel-panic.org/cgi-bin/mailman/listinfo/kplug-list
