6 ms·

SQLAlchemy supports multiple-column primary keys, Django ORM does not. What's more to think about?

by wulczer 15y ago

cturner 15y ago
If I'm designing a schema, I always have a primary key column called "id" with an autoincrement sequence against it. Sometimes I'll have a domain table, with key varchar, description varchar. Perhaps you deal with existing systems you didn't design. Aside from that - where is the cause to use a multi-column primary key?
- IgorPartola 15y ago
  The canonical example is a when you have a many-to-many relationship. You have table A, table B and a join table C, with columns (a_id, b_id). This table by itself is meaningless and does not need it's on primary key. Thus, you want to set your primary key to be (a_id, b_id) or (b_id, a_id) depending on how you want the data clustered on disk. You then also want to add a unique key with the opposite order of columns to make lookups fast when going the other way.
  - cturner 15y ago
    Could you use a table design of id|fk_a_id|fk_b_id, and then complement with indexes to get equiv. performance of the direct composite primary key? Assuming so, I think composite key is premature optimisation.
    - IgorPartola 15y ago
      Sort of. First of all, this is all about not creating unnecessary entities in your DB. The performance benefits usually follow, but are not necessarily the goal. What happens (at least in MySQL) is that now you have three indecies instead of two: (id), (a_id, b_id) and (b_id, a_id). So, for a large table you are taking up more RAM than you need to, since the index is likely to be stored in RAM. Second, often times the DB will try to optimize lookups against primary keys by clustering the records that are close on the index tree to be close on your HDD. This can lead to increases in performance for large tables. See: http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.html http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.ht...
- ismarc 15y ago
  When you're dealing with non-third normal form db schemas for performance reasons where materialized views won't provide the gains you need. That said, I've only seen a need for them about 3 times in my career, but I imagine they're much more applicable in data warehousing scenarios.
- zzzeek 15y ago
  A many-to-many table as IgorPartola mentioned, other examples include versioned or temporal storage, i.e. a "history" table where the primary key is the PK of the parent table + a version id. SQLAlchemy can also map to views or selects - if a view or select is against multiple tables, now you have a composite primary key by nature. A core piece of SQLAlchemy is that we select rows from many things besides physical tables.
  - parfe 15y ago
    >A core piece of SQLAlchemy is that we select rows from many things besides physical tables. Your sentence is a key part about the differences between Django ORM and SqlALchemy. Every article should include it.
- wulczer 15y ago
  There are strong opinions on it, but I firmly believe that "always have an autoincremental id" is a very wrong approach. Read http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-i-7327 http://it.toolbox.com/blogs/database-soup/primary-keyvil-par... (an often-quoted series of blog posts on why surrogate numeric keys are evil). In general, if you have an entity with a well-defined natural key (users and their logins, shipments and their tracking codes), why would you use an autogenerated key that's meaningless? Case in point, my current schema, designed from scratch. Users can have multiple dashboards, each dashboard has a label. A user cannot have multiple dashboards with the same label, but the two users can of course use the same label for their dashboards (like "sales" or "mydashboard"). The dashboard's table primary key is (username, label) - that pair uniquely identifies a dashboard, so it's a perfect PK candidate. Also, as already noted in one response, many-to-many relationships are typically modelled by an intermediate table that has foreign keys to both sides of the many-to-many, and its foreign key is the sum of these foreign keys. EDIT: added a concrete example
  - zzzeek 15y ago
    DBAs will emphasize surrogate integer primary keys largely for performance reasons. On SQL Server and others, their performance and size usage in indexes and such is well defined and well optimized. I acutally had a few natural PKs suggested in my current schema and the DBAs insisted that every table primary key on a surrogate. Surrogate PKs also allow you to not have to worry about ON UPDATE cascades. A gig I did a few years ago we decided to use some natural PKs, but as is reality, these PKs had to change all the time, not as much as part of regular operation, but more as we modified how the site worked and referenced information as well as when we had to go in and correct data that was mis-labeled (it was a content aggregation site sucking in many gigs of data per day). Plus the natural PKs made our indexes, primary key as well as all the referencing foreign key indexes, huge and poorly performing.
    - wulczer 15y ago
      Yeah, these might all be valid reasons to use surrogate keys. I'm not saying there are none, just that always using one often leads to design mistakes. Incidentally, part III of the keyvil blog posts talks about reasons to use surrogates :) As always, nothing's black or white, sometimes surrogates are OK, sometimes they're not. But I see schemas abusing surrogates much more often than schemas abusing natural keys...
- brown9-2 15y ago
  Even if your design doesn't have a use case for it - isn't it reassuring to know that your ORM has the functionality available if it is ever needed?
- pyre 15y ago
  This was just explained to me by our DBA for a data warehouse (technically a 'data mart' not a data warehouse if you want to pedantic) that we're building. Say you've got two tables: table a ( id integer primary key, data text ) table b ( id integer primary key, postal_code text ) In reality, postal_code is a natural key, so there's no need to the surrogate key b.id but if you insist on using it that way you'll end up with this: select * from a inner join b on a.id = b.id where b.postal_code = '97000' The query plan for this will end up something like: NESTED LOOP TABLE B WHERE postal_code = '97000' TABLE A That means that it will do a full table scan of table a for each record in table b where postal_code = '97000'. There are a couple of ways to mitigate this: 1. Use postal_code as the key. 2. Perform two queries: select * from b where postal_code = '97000'; and use the result of that to filter table a select * from a where id in (*); -- or similar So now it only does a single pass table scan of table a 3. Use Oracle which has something called a 'star join' which basically does option #2 automatically for you. Obviously this is the simple case. Things get more complex the more tables you add in.
  - j-kidd 15y ago
    > That means that it will do a full table scan of table a for each record in table b where postal_code = '97000'. The query plan would be either: NESTED LOOP TABLE B WHERE postal_code = '97000' TABLE A (index scans) or: TABLE A (1 full table scan) TABLE B WHERE postal_code = '97000' What database do you use that would perform multiple full table scans?
cbs 15y ago
>SQLAlchemy supports multiple-column primary keys, Django ORM does not. What's more to think about? Exactly. If it wasn't for that restriction we would have begun a test adoption. As soon as I saw that multiple-column keys were a mystery to it, not only did it send me packing but made me question the fitness of django in general, I feel like we may have dodged a serious bullet there (if they can't even get that right, what other surprises lie in wait?)
- madewulf 15y ago
  It is possible to specify that some column values must be unique together, which makes this point pretty moot for any new project. That said, if you are planning to work on existing schemas, this can really be a headache. About other nasty surprises, I have been using Django a lot extensively over the last 4 years, and I can honestly say I haven't find any and I dare you to find a web framework that will allow you to bootstrap a project much faster than Django .

SQLAlchemy supports multiple-column primary keys, Django ORM does not. What's more to think about?

by wulczer 15y ago

SQLAlchemy supports multiple-column primary keys, Django ORM does not. What's more to think about?

If I'm designing a schema, I always have a primary key column called "id" with an autoincrement sequence against it. Sometimes I'll have a domain table, with key varchar, description varchar. Perhaps you deal with existing systems you didn't design. Aside from that - where is the cause to use a multi-column primary key?

IgorPartola 15y ago
The canonical example is a when you have a many-to-many relationship. You have table A, table B and a join table C, with columns (a_id, b_id). This table by itself is meaningless and does not need it's on primary key. Thus, you want to set your primary key to be (a_id, b_id) or (b_id, a_id) depending on how you want the data clustered on disk. You then also want to add a unique key with the opposite order of columns to make lookups fast when going the other way.
- cturner 15y ago
  Could you use a table design of id|fk_a_id|fk_b_id, and then complement with indexes to get equiv. performance of the direct composite primary key? Assuming so, I think composite key is premature optimisation.
  - IgorPartola 15y ago
    Sort of. First of all, this is all about not creating unnecessary entities in your DB. The performance benefits usually follow, but are not necessarily the goal. What happens (at least in MySQL) is that now you have three indecies instead of two: (id), (a_id, b_id) and (b_id, a_id). So, for a large table you are taking up more RAM than you need to, since the index is likely to be stored in RAM. Second, often times the DB will try to optimize lookups against primary keys by clustering the records that are close on the index tree to be close on your HDD. This can lead to increases in performance for large tables. See: http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.html http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.ht...
ismarc 15y ago
When you're dealing with non-third normal form db schemas for performance reasons where materialized views won't provide the gains you need. That said, I've only seen a need for them about 3 times in my career, but I imagine they're much more applicable in data warehousing scenarios.
zzzeek 15y ago
A many-to-many table as IgorPartola mentioned, other examples include versioned or temporal storage, i.e. a "history" table where the primary key is the PK of the parent table + a version id. SQLAlchemy can also map to views or selects - if a view or select is against multiple tables, now you have a composite primary key by nature. A core piece of SQLAlchemy is that we select rows from many things besides physical tables.
- parfe 15y ago
  >A core piece of SQLAlchemy is that we select rows from many things besides physical tables. Your sentence is a key part about the differences between Django ORM and SqlALchemy. Every article should include it.
wulczer 15y ago
There are strong opinions on it, but I firmly believe that "always have an autoincremental id" is a very wrong approach. Read http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-i-7327 http://it.toolbox.com/blogs/database-soup/primary-keyvil-par... (an often-quoted series of blog posts on why surrogate numeric keys are evil). In general, if you have an entity with a well-defined natural key (users and their logins, shipments and their tracking codes), why would you use an autogenerated key that's meaningless? Case in point, my current schema, designed from scratch. Users can have multiple dashboards, each dashboard has a label. A user cannot have multiple dashboards with the same label, but the two users can of course use the same label for their dashboards (like "sales" or "mydashboard"). The dashboard's table primary key is (username, label) - that pair uniquely identifies a dashboard, so it's a perfect PK candidate. Also, as already noted in one response, many-to-many relationships are typically modelled by an intermediate table that has foreign keys to both sides of the many-to-many, and its foreign key is the sum of these foreign keys. EDIT: added a concrete example
- zzzeek 15y ago
  DBAs will emphasize surrogate integer primary keys largely for performance reasons. On SQL Server and others, their performance and size usage in indexes and such is well defined and well optimized. I acutally had a few natural PKs suggested in my current schema and the DBAs insisted that every table primary key on a surrogate. Surrogate PKs also allow you to not have to worry about ON UPDATE cascades. A gig I did a few years ago we decided to use some natural PKs, but as is reality, these PKs had to change all the time, not as much as part of regular operation, but more as we modified how the site worked and referenced information as well as when we had to go in and correct data that was mis-labeled (it was a content aggregation site sucking in many gigs of data per day). Plus the natural PKs made our indexes, primary key as well as all the referencing foreign key indexes, huge and poorly performing.
  - wulczer 15y ago
    Yeah, these might all be valid reasons to use surrogate keys. I'm not saying there are none, just that always using one often leads to design mistakes. Incidentally, part III of the keyvil blog posts talks about reasons to use surrogates :) As always, nothing's black or white, sometimes surrogates are OK, sometimes they're not. But I see schemas abusing surrogates much more often than schemas abusing natural keys...
brown9-2 15y ago
Even if your design doesn't have a use case for it - isn't it reassuring to know that your ORM has the functionality available if it is ever needed?
pyre 15y ago
This was just explained to me by our DBA for a data warehouse (technically a 'data mart' not a data warehouse if you want to pedantic) that we're building. Say you've got two tables: table a ( id integer primary key, data text ) table b ( id integer primary key, postal_code text ) In reality, postal_code is a natural key, so there's no need to the surrogate key b.id but if you insist on using it that way you'll end up with this: select * from a inner join b on a.id = b.id where b.postal_code = '97000' The query plan for this will end up something like: NESTED LOOP TABLE B WHERE postal_code = '97000' TABLE A That means that it will do a full table scan of table a for each record in table b where postal_code = '97000'. There are a couple of ways to mitigate this: 1. Use postal_code as the key. 2. Perform two queries: select * from b where postal_code = '97000'; and use the result of that to filter table a select * from a where id in (*); -- or similar So now it only does a single pass table scan of table a 3. Use Oracle which has something called a 'star join' which basically does option #2 automatically for you. Obviously this is the simple case. Things get more complex the more tables you add in.
- j-kidd 15y ago
  > That means that it will do a full table scan of table a for each record in table b where postal_code = '97000'. The query plan would be either: NESTED LOOP TABLE B WHERE postal_code = '97000' TABLE A (index scans) or: TABLE A (1 full table scan) TABLE B WHERE postal_code = '97000' What database do you use that would perform multiple full table scans?

cbs 15y ago

>SQLAlchemy supports multiple-column primary keys, Django ORM does not. What's more to think about? Exactly. If it wasn't for that restriction we would have begun a test adoption. As soon as I saw that multiple-column keys were a mystery to it, not only did it send me packing but made me question the fitness of django in general, I feel like we may have dodged a serious bullet there (if they can't even get that right, what other surprises lie in wait?)

madewulf 15y ago
It is possible to specify that some column values must be unique together, which makes this point pretty moot for any new project. That said, if you are planning to work on existing schemas, this can really be a headache. About other nasty surprises, I have been using Django a lot extensively over the last 4 years, and I can honestly say I haven't find any and I dare you to find a web framework that will allow you to bootstrap a project much faster than Django .