4 ms·

Maybe this is a MySQL noob question, but why do they have to do this complicated thing to get a unique id for each project backer? CREATE TABLE project_bac

Maybe this is a MySQL noob question, but why do they have to do this complicated thing to get a unique id for each project backer? CREATE TABLE project_backers ( # The number they're looking for. id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, # user_table fk syntax here. project_id INTEGER # project_table fk syntax here. ) Insert into this table every time someone backs a project and you get the monotonically ascending number for free. Again, I'm a total SQL noob, so maybe this isn't really doing what they want.

nknighthb 14y ago
I suspect they were trying to make the data look reasonably nice for project creators. Your (not incorrect) solution has the downside of ever-increasing numbers quickly reaching into the millions or billions, while their solution has numbers no larger than the number of backers on a project (unlikely to be more than tens of thousands). They also save the storage cost of going to a BIGINT, but I doubt that was a major factor.
- jamesaguilar 14y ago
  True, but couldn't you pretty easily derive the logical ID via some sort of pagination scheme? That is, when you really want to see it at all. It seems like this would be a pretty low cost query as long as you set your indices up properly, and I'm assuming for the hundred-thousand backer projects you'll never need to display every backer in order. Another slight concern is how big this project_backers table would get, since it's a combination. Let's assume every project has an average of 10k backers and there are about 100k projects. That makes 1B relationships, with O 128 bits per row (plus whatever associated data). 16 GB. Not too bad for such an important relationship. Even with 100x growth and no garbage collection or archiving, you could fit it on (a couple of) flash disks for the foreseeable future. I don't know if MySQL supports delta encoding but that would also probably make the table cheaper to store.
  - nknighthb 14y ago
    You could, but then you have multiple identifiers for the same object, and you end up having to decide "wait, which ID do I want here?", and during long nights when you're hopped up on five cans of Red Bull, the numbers start to run together, and you don't know if you're looking at a real ID or a logical ID, and even when you do know, you end up running queries directly against the database trying to convince yourself that the mapping can't possibly be correct. Been there, done that, consumed a lifetime supply of Mountain Dew in a year, and handed in a multi-page resignation letter[1]. I'd rather use their solution, which ultimately minimizes the overall complexity of the system. [1] OK, the resignation letter wasn't really about IDs, but the ID problems were a symptom of larger problems.
    - jamesaguilar 14y ago
      Interesting. I would expect that the logical id would be displayed only, never stored, but that would make debugging display issues difficult. Thank you for your thoughts. It's helpful to hear the thoughts of other database professionals, especially in domains where I have no knowledge, like relational databases.
- mbell 14y ago
  They mention transactions so I would assume they are using InnoDB in which case you have to have a primary key, if you don't define one InnoDB will either use the first unique not null column it finds or failing that, create one using the internal 6 byte rowID. They could have used a composite primary key on (project_id, backer_id) but that can create other problems such as blowing up the size of your secondary indexes (primary index is included in all secondary indexes in innoDB). So I doubt they were too worried about the storage space compared to other options and probably already had a unique ID column for every row. If I had to guess, they wanted this column so they could use it to get the number of the backers on any project very quickly. InnoDB's COUNT() performance historically is pretty terrible, especially with large tables, I'm guessing they were trying to avoid that, in addition to being able to provide the project owners with a 'backer ID' that is more human digestible. That said, the count could be handled with a trigger and there is a trick using LAST_INSERT_ID(expr) for generating the sequence: Create a column somewhere to hold the sequence value, for example in a backer_sequence_id column in the project table then: UPDATE project SET backer_sequence_id=LAST_INSERT_ID(backer_sequence_id+1) WHERE id = ?; SELECT LAST_INSERT_ID(); <----returns the sequence ID that was created It does lock the row in the project table if this is inside a larger transaction but that shouldn't be too hard to avoid. LAST_INSERT_ID is handled per client, so its multi-connection safe. The above works inside a trigger also so the client code wouldn't need to think about it.
falcolas 14y ago
autoincrement only works on one database, and is not guaranteed to be unique across multiple schemas. Depending on their schema and sharding techniques, it's probably easier for them to create the unique ids in the application than rely on the DB.

nknighthb 14y ago

I suspect they were trying to make the data look reasonably nice for project creators. Your (not incorrect) solution has the downside of ever-increasing numbers quickly reaching into the millions or billions, while their solution has numbers no larger than the number of backers on a project (unlikely to be more than tens of thousands). They also save the storage cost of going to a BIGINT, but I doubt that was a major factor.

jamesaguilar 14y ago
True, but couldn't you pretty easily derive the logical ID via some sort of pagination scheme? That is, when you really want to see it at all. It seems like this would be a pretty low cost query as long as you set your indices up properly, and I'm assuming for the hundred-thousand backer projects you'll never need to display every backer in order. Another slight concern is how big this project_backers table would get, since it's a combination. Let's assume every project has an average of 10k backers and there are about 100k projects. That makes 1B relationships, with O 128 bits per row (plus whatever associated data). 16 GB. Not too bad for such an important relationship. Even with 100x growth and no garbage collection or archiving, you could fit it on (a couple of) flash disks for the foreseeable future. I don't know if MySQL supports delta encoding but that would also probably make the table cheaper to store.
- nknighthb 14y ago
  You could, but then you have multiple identifiers for the same object, and you end up having to decide "wait, which ID do I want here?", and during long nights when you're hopped up on five cans of Red Bull, the numbers start to run together, and you don't know if you're looking at a real ID or a logical ID, and even when you do know, you end up running queries directly against the database trying to convince yourself that the mapping can't possibly be correct. Been there, done that, consumed a lifetime supply of Mountain Dew in a year, and handed in a multi-page resignation letter[1]. I'd rather use their solution, which ultimately minimizes the overall complexity of the system. [1] OK, the resignation letter wasn't really about IDs, but the ID problems were a symptom of larger problems.
  - jamesaguilar 14y ago
    Interesting. I would expect that the logical id would be displayed only, never stored, but that would make debugging display issues difficult. Thank you for your thoughts. It's helpful to hear the thoughts of other database professionals, especially in domains where I have no knowledge, like relational databases.
mbell 14y ago
They mention transactions so I would assume they are using InnoDB in which case you have to have a primary key, if you don't define one InnoDB will either use the first unique not null column it finds or failing that, create one using the internal 6 byte rowID. They could have used a composite primary key on (project_id, backer_id) but that can create other problems such as blowing up the size of your secondary indexes (primary index is included in all secondary indexes in innoDB). So I doubt they were too worried about the storage space compared to other options and probably already had a unique ID column for every row. If I had to guess, they wanted this column so they could use it to get the number of the backers on any project very quickly. InnoDB's COUNT() performance historically is pretty terrible, especially with large tables, I'm guessing they were trying to avoid that, in addition to being able to provide the project owners with a 'backer ID' that is more human digestible. That said, the count could be handled with a trigger and there is a trick using LAST_INSERT_ID(expr) for generating the sequence: Create a column somewhere to hold the sequence value, for example in a backer_sequence_id column in the project table then: UPDATE project SET backer_sequence_id=LAST_INSERT_ID(backer_sequence_id+1) WHERE id = ?; SELECT LAST_INSERT_ID(); <----returns the sequence ID that was created It does lock the row in the project table if this is inside a larger transaction but that shouldn't be too hard to avoid. LAST_INSERT_ID is handled per client, so its multi-connection safe. The above works inside a trigger also so the client code wouldn't need to think about it.

falcolas 14y ago

autoincrement only works on one database, and is not guaranteed to be unique across multiple schemas. Depending on their schema and sharding techniques, it's probably easier for them to create the unique ids in the application than rely on the DB.