On 7/8/10 9:03 AM, Victor Subervi wrote: > mysql> describe products Store; > +-------+-------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +-------+-------------+------+-----+---------+-------+ > | Store | varchar(40) | NO | MUL | NULL | | > +-------+-------------+------+-----+---------+-------+ > 1 row in set (0.00 sec) > > mysql> describe personalDataKeys Store; > +-------+-------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +-------+-------------+------+-----+---------+-------+ > | Store | varchar(40) | NO | MUL | NULL | | > +-------+-------------+------+-----+---------+-------+ > 1 row in set (0.00 sec) > > They both use innodb. They're both indexed. I was thinking after getting > your email that maybe I'd set the varchars to different lengths, but no.
A foreign key isn't about the schema, per se; its not about the varchar's being different lengths (as they discard trailing padding)-- its about *data*. True, if you had varchar(20) and varchar(40), then if any string longer then 20 wouldn't ever pass -- but that's really secondary. (That's not saying a database may refuse to accept a FK if data types are mismatched) If "personalDataKeys" has a foreign key connecting it to "products", then you can't add something to personalDataKeys with store = "specialty" unless something already exists in "products" with store = "speciality"; > However... > > mysql> select * from products; > Empty set (0.00 sec) > > Is it that I can't insert into personalDataKeys until I've first done so > in products? Yes, that's precisely what foreign keys do. > That wasn't necessary, since personalDataKeys only > needs to be associated with personalData, so I dropped and recreated the > table, updating personalDataKeys foreign key to reference personalData; > however, once again: Are you certain this is what you want? It sounds like you may be using foreign keys without fully understanding what they are. Think of them like a big arrow. If you define a foreign key in personalDataKeys, referencing personalData, you should picture a large arrow pointing from personalDataKeys to personalData. It's pointing because the "constraint" created by the foreign key means, "Every record in this table, personalDataKeys, has a column which *must* exist in its referenced table, personalData, before that record is allowed to be added." A foreign key isn't just a description of a relationship: its a strict rule, declaring that a certain field in one table *actually* refers directly to a *specific* row in *another* table: therefore, this field can't be allowed to be any value which doesn't exist already in that other table. A "primary key" lets you uniquely identify a certain row in one table. A "foreign key" lets you identify a certain row in *another table*, that this table ultimately depends on. > In personalDataKeys I store which fields will be required for a given > store as it relates to personal data. For example, if there is a > pharmacy with users 'doctors' and 'patients', certain fields in > personalData will be required for one but not the other, and this needs > to be inserted into personalDataKeys. My concern here is that you're making *columns* which are "store-dependent", such as sometimes in one store, personalData will have a column/field named "foo", but another store it won't use "foo" but instead use "bar", depending on how the store itself is configured. I'd refer you back to my previous email which described two schemes to record "store-dependant" data: one using a separate table for each store type, another using a generic key/value table. Having one big table with a big mix of columns that various store configurations pick and choose seems like a very inflexible design. Additionally (if you do keep this design), these two tables you described seem to make the columns that are used tied to *users*, not *stores*. The key for personalDataKeys is (Store, User): but isn't it the case that for a certain kind of store (i.e., a pharmacy), /all/ users in that store will have the same fields in personalData be relevant? So shouldn't "personalDataKeys" really be "storeDataKeys"? I.e., a configuration of the store itself of what data keys it considers relevant. > All of this, however, obviously > happens before any data is actually entered into either personalData or > products. Yeah, the constraints and such happen before data is entered. But once they are created, you have to actual enter data in the correct order. The constraints enforce consistency so programmer-error can't introduce data into the tables which is out of whack with the data layout. -- Stephen Hansen ... Also: Ixokai ... Mail: me+list/python (AT) ixokai (DOT) io ... Blog: http://meh.ixokai.io/
signature.asc
Description: OpenPGP digital signature
-- http://mail.python.org/mailman/listinfo/python-list