Menu
▾
▴
sqlobject-discuss
|
From: Edmund L. <el...@in...> - 2003-04-10 05:38:51
|
Steve Holden wrote: >> 5. Your assertion that MySQL does not support transactions is out of date, >> since they can be supported by databases built from InnoDB tables, I believe >> (or have I imagined this?). Be aware that InnoDB is not free for commercial use, unlike PostgreSQL, Firebird, SAPDB, etc. If you use InnoDB in any product that the user must pay you for, you must buy a commercial license. Tacking on something that does transactions does not make MySQL transaction safe. See http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci788645,00.html?FromTaxonomy %2Fpr%2F284872 Ian Bicking wrote: >I don't know. I like single, simple primary keys. They work well. If >the table is really so minimal that it can't stand to have an extra >field for the id, then I suspect a SQLObject class is too heavy for it >(like with the join tables). Of course, when adapting to a pre-existing >schema you might not have as many options. Maybe it wouldn't be that >hard to fudge it (maybe make the ID into a tuple), but SQLObject really >expects for each object to have a single ID. Composite keys are used an awful lot in any schema of moderate complexity, and they are important (when used with foreign key constraints) for enforcing relational integrity. If the aim is for SQLObject to support complex projects, then composite key support would be pretty important. ...Edmund. |
|
From: Edmund L. <el...@in...> - 2003-04-11 17:34:34
|
On 04/10/2003 02:06:44 AM Ian Bicking wrote:
>On Thu, 2003-04-10 at 00:38, Edmund Lian wrote:
>> Composite keys are used an awful lot in any schema of moderate
complexity,
>> and they are important (when used with foreign key constraints) for
>> enforcing relational integrity. If the aim is for SQLObject to support
>> complex projects, then composite key support would be pretty important.
>
>Can you describe some situations where they'd be used? For instance, if
>either key was modified at any time that'd cause a lot of problems for
>SQLObject. If not, then what would be the problem with a third simple
>ID/PRIMARY KEY column?
Hmmm... Here's a contrived example that typifies how I use them.
create table category (
name varchar(100),
--
constraint category_pk
primary key (name)
);
create table item (
name varchar(100),
--
constraint item_pk
primary key (name)
);
create table category_item_map (
category varchar(100),
item varchar (100),
--
constraint category_item_pk
primary key (category, item),
constraint category_item_category_fk
foreign key (category) references category (name)
on update cascade
on delete cascade,
constraint category_item_item_fk
foreign key (item) references item (name)
on update cascade
on delete cascade
);
The argument against using real rather than surrogate primary keys (which
is what you are doing) is typically that primary keys have business
meaning, and that changing them becomes difficult because the changes in
one table must be somehow cascade through all the other tables that use the
primary keys of one table as foreign keys.
There are really two separate issues here. Let's look at the first
one--that it is bad to have primary keys which have business meaning. Yes,
they do have business meaning, because they are integrity constraints.
That's what you want. Without integrity constraints, the data in the DB has
no meaning.
Constraints are to data as are grammar is to words. Without grammar, a page
of written text would be just a jumble of words. You derive meaning from
written text only if the reader and writer agree to certain rules of
grammar, which constrains the words to have certain order, roles, etc. To
push the point further, consider three words: "eat", "I", and "meat".
Without grammatical constraints, and of the following is possible:
meat eat I
eat meat I
meat I eat
meat eat I
I meat eat
I eat meat
Now suppose we agree to some constraints--grammatical rules. The rules we
agree to are:
Phrases must be in subject-verb-object order
Action words are verbs
Pronouns are subject nouns
+ a few other rules which I won't bore you with.
Once we constrain the words with grammar, the only valid combination is: I
eat meat
So, it is critically important to have constraints in the DB. They
determine the semantics of the data almost completely. To use surrogate
keys instead of real keys weakens the integrity constraints substantially.
The second issue--that real primary keys are troublesome because when the
business meaning changes, they have to be changed, and this is tough to do.
This argument might be true in Oracle, but it is not true in PostgreSQL and
a few other databases that support the "on update cascade" clause in
foreign key constraints.
With the table definitions I have above, an update or deletion to a primary
key in the category or item table cascade through to all the tables that
use the updated primary key as a foreign key. This is so very nice, and is
one reason why I say that Oracle is actually more backward than PostgreSQL
in some respects.
...Edmund.
|
|
From: Edmund L. <el...@in...> - 2003-04-11 17:41:22
|
On 04/11/2003 04:45:37 AM Ian wrote: >Oh, I forgot to reply to this too. This is definitely on my mind. I >also wrote FunFormKit (funformkit.sf.net), which is a form >rendering/validation library. I'll be the first to admit it's too >complex, and I've wanted to reimplement it for some time... now my focus >is on reimplementing it with SQLObject in mind (though without any >actual ties). It probably wouldn't be that hard to do this rendering >right now with FFK -- Luke has mentioned at least using some stuff from >FFK with SQLObject -- but SQLObject's design makes me happy, and I want >the entire system to make me happy, and FFK doesn't do that for me. Amen. Definitely needs to be refactored/rewritten! It's very hard to extend/maintain in its current form. But, the interface to the Webware servlet is simple (at least for v0.4 and above), which is what made me use it instead of FormKit. ...Edmund. |
|
From: Ian B. <ia...@co...> - 2003-04-10 06:05:55
|
On Thu, 2003-04-10 at 00:38, Edmund Lian wrote: > Composite keys are used an awful lot in any schema of moderate complexity, > and they are important (when used with foreign key constraints) for > enforcing relational integrity. If the aim is for SQLObject to support > complex projects, then composite key support would be pretty important. Can you describe some situations where they'd be used? For instance, if either key was modified at any time that'd cause a lot of problems for SQLObject. If not, then what would be the problem with a third simple ID/PRIMARY KEY column? Ian |
|
From: Brad B. <br...@bb...> - 2003-04-10 23:22:55
|
On 04/10/03 01:06, Ian Bicking wrote: > On Thu, 2003-04-10 at 00:38, Edmund Lian wrote: > > Composite keys are used an awful lot in any schema of moderate complexity, > > and they are important (when used with foreign key constraints) for > > enforcing relational integrity. If the aim is for SQLObject to support > > complex projects, then composite key support would be pretty important. > > Can you describe some situations where they'd be used? For instance, if > either key was modified at any time that'd cause a lot of problems for > SQLObject. If not, then what would be the problem with a third simple > ID/PRIMARY KEY column? I use concatenated keys all the time. e.g. Site site_id PK ... User user_id PK ... UserSite user_id FK site_id FK (together they're the PK) ... or Project project_id PK ... Worker worker_id PK ... ProjectWorker project_id FK worker_id FK (together they're the PK) hourly_rate ... and so on. The problem with not having complex primary key support is not that it makes it impossible to write a certain kind of application but that it creates extra unnecessary, boring work for the programmer to have to hardcode the validation checks (and the unit tests for them) to ensure -- taking from one example above -- that a user hasn't been granted access to a site twice. -- Brad Bollenbach BBnet.ca |
|
From: Bud P. B. <bu...@si...> - 2003-04-11 07:46:30
|
On Thu, 10 Apr 2003 18:22:11 -0500 Brad Bollenbach <br...@bb...> wrote: > Project > project_id PK > ... > > Worker > worker_id PK > ... > > ProjectWorker > project_id FK > worker_id FK > (together they're the PK) > hourly_rate > ... > > and so on. But in my experience, it gets hard if these keys have to change--and since they carry business meaning, it is not always inevitable (people even in rare cases change social security numbers...). For example, assume you reassign a project to another worker... If there is a key without any business meaning whatsoever, changing is MUCH easier... --b |
|
From: Ian B. <ia...@co...> - 2003-04-11 06:36:22
|
On Thu, 2003-04-10 at 18:22, Brad Bollenbach wrote: > On 04/10/03 01:06, Ian Bicking wrote: > > On Thu, 2003-04-10 at 00:38, Edmund Lian wrote: > > > Composite keys are used an awful lot in any schema of moderate complexity, > > > and they are important (when used with foreign key constraints) for > > > enforcing relational integrity. If the aim is for SQLObject to support > > > complex projects, then composite key support would be pretty important. > > > > Can you describe some situations where they'd be used? For instance, if > > either key was modified at any time that'd cause a lot of problems for > > SQLObject. If not, then what would be the problem with a third simple > > ID/PRIMARY KEY column? > > I use concatenated keys all the time. > > e.g. > > Site > site_id PK > ... > > User > user_id PK > ... > > UserSite > user_id FK > site_id FK > (together they're the PK) > ... This seems like a many-to-many relation, which SQLObject doesn't represent as a Python object, and which doesn't require any ID. > The problem with not having complex primary key support is not that it > makes it impossible to write a certain kind of application but that it > creates extra unnecessary, boring work for the programmer to have to > hardcode the validation checks (and the unit tests for them) to ensure > -- taking from one example above -- that a user hasn't been granted > access to a site twice. But you can still declare the composite columns to be unique with them being the primary key. If it's a table that's really as derivitive as these, I don't think the rows should be represented as Python objects. If RelatedJoin doesn't work like you'd need, maybe a different join should be made, but I think that's where tables like UserSite belong. Ian |
|
From: Bud P. B. <bu...@si...> - 2003-04-11 07:47:53
|
On 11 Apr 2003 01:37:10 -0500 Ian Bicking <ia...@co...> wrote: > > Site > > site_id PK > > ... > > > > User > > user_id PK > > ... > > > > UserSite > > user_id FK > > site_id FK > > (together they're the PK) > > ... > > This seems like a many-to-many relation, which SQLObject doesn't > represent as a Python object, and which doesn't require any ID. The difference to many-to-many relationships (in my understanding) is that the "breakup" table has many more columns than just necessary for linking. --b /----------------------------------------------------------------- | Bud P. Bruegger, Ph.D. | Sistema (www.sistema.it) | Via U. Bassi, 54 | 58100 Grosseto, Italy | +39-0564-411682 (voice and fax) \----------------------------------------------------------------- |
|
From: Brad B. <br...@bb...> - 2003-04-11 12:59:30
|
On 04/11/03 09:45, Bud P. Bruegger wrote: > On Thu, 10 Apr 2003 18:22:11 -0500 > Brad Bollenbach <br...@bb...> wrote: > > > Project > > project_id PK > > ... > > > > Worker > > worker_id PK > > ... > > > > ProjectWorker > > project_id FK > > worker_id FK > > (together they're the PK) > > hourly_rate > > ... > > > > and so on. > > But in my experience, it gets hard if these keys have to change--and > since they carry business meaning, it is not always inevitable (people > even in rare cases change social security numbers...). For example, > assume you reassign a project to another worker... If there is a key > without any business meaning whatsoever, changing is MUCH easier... There's no implication in there that they do carry business meaning. I.e. project_id and worker_id could easily be integers (and each table could later have a name column, e.g. project_name, worker_name, etc.) -- Brad Bollenbach BBnet.ca |
|
From: Bud P. B. <bu...@si...> - 2003-04-11 13:09:21
|
On Fri, 11 Apr 2003 07:58:45 -0500 Brad Bollenbach <br...@bb...> wrote: > There's no implication in there that they do carry business > meaning. I.e. project_id and worker_id could easily be integers (and > each table could later have a name column, e.g. project_name, > worker_name, etc.) Hmmm. you're right! But assume there are other tables referencing ProjectWorker; say ProjectWorkerComment. Assume, they also use composite keys: project_id worker_id comment_id (where (project_id, worker_id) is a foreign key referencing ProjectWorker Then, if you reassign the project to another worker, you also have to change the composite key of the related comments. So you could say that the business meaning is not in the key itself but in the relationships you define in terms of the keys... --b |
|
From: Brad B. <br...@bb...> - 2003-04-11 13:24:36
|
On 04/11/03 15:08, Bud P. Bruegger wrote: > On Fri, 11 Apr 2003 07:58:45 -0500 > Brad Bollenbach <br...@bb...> wrote: > > > There's no implication in there that they do carry business > > meaning. I.e. project_id and worker_id could easily be integers (and > > each table could later have a name column, e.g. project_name, > > worker_name, etc.) > > Hmmm. you're right! > > But assume there are other tables referencing ProjectWorker; say > ProjectWorkerComment. Assume, they also use composite keys: > > project_id > worker_id > comment_id > > (where (project_id, worker_id) is a foreign key referencing > ProjectWorker > > Then, if you reassign the project to another worker, you also have to > change the composite key of the related comments. > > So you could say that the business meaning is not in the key itself > but in the relationships you define in terms of the keys... Hmmmm, I don't see the problem here. You have two options when assigning a different worker to the project: 1. A simple update on worker_id, in which case all previous comments in your comments table will now look like they refer to that new worker_id (which may or may not be what you want). 2. Inserting a new row into ProjectWorker, which provides separation between the comments related to each worker who's done work on the project. In either case, this will not affect anything in comments, because this is a table that would probably look like: Comments comment_id comment_desc ... etc. It doesn't care what kind of update-fu you do on the composite key of ProjectWorker (and ProjectWorker probably doesn't care either, unless -- of course -- the update tries to duplicate an existing project/worker pair). -- Brad Bollenbach BBnet.ca |
|
From: Bud P. B. <bu...@si...> - 2003-04-11 13:45:47
|
On Fri, 11 Apr 2003 08:23:51 -0500 Brad Bollenbach <br...@bb...> wrote: > In either case, this will not affect anything in comments, because > this is a table that would probably look like: > > Comments > comment_id > comment_desc > ... > etc. If this is your comments table, there are no problems. However, the problem I try to illustrate uses the following table: CREATE TABLE ProjectWorkerComment( project_id INT, worker_id INT, comment_id INT, PRIMARY KEY (project_id, worker_id, comment_id), FOREIGN KEY (project_id, worker_id) REFERENCES ProjectWorker); It may not be a realistic example, but I was attempting to quickly think of something to add additional levels to the hierarchy of composite foreign key relationships. If you have such a hierachy, changing relationships at a higher level (for business logic reasons) implies that you change the composite keys of all related tables at lower levels. --b |
|
From: Ian B. <ia...@co...> - 2003-04-11 19:31:50
|
Okay, I got all confused because both of your first and last names start in B, and when I was first skimming through this I thought one of you was arguing with yourself. But no... anyway, to interject, I still don't feel there's a good use case for multiple keys (except legacy schemas) On Fri, 2003-04-11 at 08:44, Bud P.Bruegger wrote: > If this is your comments table, there are no problems. However, the > problem I try to illustrate uses the following table: > > CREATE TABLE ProjectWorkerComment( > project_id INT, > worker_id INT, > comment_id INT, > PRIMARY KEY (project_id, worker_id, comment_id), > FOREIGN KEY (project_id, worker_id) REFERENCES ProjectWorker); In this example you should have a join, in the spirit of RelatedJoin. Project would have a workerComment method that would return a list of (worker, comment) tuples, and Worker and Comment would have similar methods. So long as a table only contains references to other tables, it should be represented by a Join of some sort, the rows of that table should not be Python objects. After looking through examples, I feel increasingly confident that this intuition is correct -- rows without content of their own are derivative, and should be treated as such. Ian |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X