4 ms·

I feel the whole debate is overkill: 99% of businesses/systems will never have so much data that they NEED to use uuid's. I personally don't like using integers

I feel the whole debate is overkill: 99% of businesses/systems will never have so much data that they NEED to use uuid's. I personally don't like using integers for keys either as I've been burnt by them before. I also doubt any software I build today or have built in the last 10 years will be used 100 years from now. Recently I built a new system (typical business-type backend) and forced to use sqlite + C# + dapper. Using this combination I cannot use guid/uuid as dapper cannot properly map it back to c# from sqlite, and my dislike of int's got me thinking. I have a random string generator (have used it for years for things like OTP's and other reference numbers), where I give it an alphabet + length of the desired string. Using 8 to 12 characters, I can get a few million unique permutations. That is, if used as a primary key, few million per database table. Then I hear in the back of my head, guys from work who would argue I would run out of unique combinations or would have to do lookups to see if they exist. So I decided slap the year and month on it as a prefix, so a key might look like this: 2105HSUAMWPA. This gets indexed really well too and there is some inherent information that can be seen from looking at the key: Year 21, Month 5 and then the unique bits.It's basically 4 lines of code that gets called on every new database entity. I think it will be easy to shard/partition the data too if the need arise in the future, by simply looking at the first 4 digits. Thus to summarize: Data is sliced by entity type (customer, invoice, etc), then by date (2105 for May 2021) then by unique string. What do you guys think about this approach? Anyone been burnt by something like this?

hardwaresofton 5y ago
> Recently I built a new system (typical business-type backend) and forced to use sqlite + C# + dapper. Using this combination I cannot use guid/uuid as dapper cannot properly map it back to c# from sqlite Are you sure about this? This is pretty poor of a well known solution in the ORM world, SQLite or not. If you were going for sortability/understandability then I understand slapping your own together, but why not generate v1/v4/v6[0] UUIDs in your application and then send them along to teh database, possibly prefixed with whatever you want it to be sorted by (though IMO you should just add that metadata to the thing being saved and sort on that properly)? [0]: http://gh.peabody.io/uuidv6/ http://gh.peabody.io/uuidv6/
- BatteryMountain 5y ago
  The problem is when reading the data back with Dapper (writing works fine) and it has to parse the guid stored in sqlite back into a .net Guid. Thus I resorted to making the column type text and ToString() all the Guid's before writing, but that's pretty gross. With a normal string field, sqlite can set it as primary key, make unique indexes on it, use it as foreign keys. Thus my attempt to use my own generator the generate id's. So far it works great but not sure if it will be fine with 5 years of usage (basically on tables that keep growing over time, like a comments table). But of a rabbit hole: https://github.com/DapperLib/Dapper/pull/1082 https://github.com/DapperLib/Dapper/pull/1082
  - hardwaresofton 5y ago
    Ahh thanks for noting this -- I've written off writing C# and PHP for the rest of my life for reasons, but this is a fascinating read.
    - BatteryMountain 5y ago
      On the other hand, C# + postgres is a super sweet combo. I don't use EntityFramework, so I don't know how well they play together, but if you write sql queries and run them from c#, it's super nice. I typically have a generic repository class that generates all the statements and it works great (I recently made the query generator able to generate inner join if I pass it filters that are not found on the table/object, based on a naming convention (any property that end in "Id" that is not the main entity Id and not a direct property, use the part before the "Id" part as the join table name) - it works great too!). I also cache certain prepared statements in a static concurrent dictionary, so I can short circuit if the it the query/insert/update has been run before for the specific object type. That way all of the string manipulation can be avoid for each query - I just use a previous statement but with different sql parameters. On every deployment the cache gets cleared because the app basically get restarted, so it won't go stale. It's very cool (at least in my mind). The only indirect rule I have is that nothing else should modify the main schema besides my migrations and not while the app is using it (using FluentMigrator code that execute on app startup). If I may ask, why don't you like C#? I totally get the php thing - I cannot stand it at all either.

hardwaresofton 5y ago

> Recently I built a new system (typical business-type backend) and forced to use sqlite + C# + dapper. Using this combination I cannot use guid/uuid as dapper cannot properly map it back to c# from sqlite Are you sure about this? This is pretty poor of a well known solution in the ORM world, SQLite or not. If you were going for sortability/understandability then I understand slapping your own together, but why not generate v1/v4/v6[0] UUIDs in your application and then send them along to teh database, possibly prefixed with whatever you want it to be sorted by (though IMO you should just add that metadata to the thing being saved and sort on that properly)? [0]: http://gh.peabody.io/uuidv6/ http://gh.peabody.io/uuidv6/

BatteryMountain 5y ago
The problem is when reading the data back with Dapper (writing works fine) and it has to parse the guid stored in sqlite back into a .net Guid. Thus I resorted to making the column type text and ToString() all the Guid's before writing, but that's pretty gross. With a normal string field, sqlite can set it as primary key, make unique indexes on it, use it as foreign keys. Thus my attempt to use my own generator the generate id's. So far it works great but not sure if it will be fine with 5 years of usage (basically on tables that keep growing over time, like a comments table). But of a rabbit hole: https://github.com/DapperLib/Dapper/pull/1082 https://github.com/DapperLib/Dapper/pull/1082
- hardwaresofton 5y ago
  Ahh thanks for noting this -- I've written off writing C# and PHP for the rest of my life for reasons, but this is a fascinating read.
  - BatteryMountain 5y ago
    On the other hand, C# + postgres is a super sweet combo. I don't use EntityFramework, so I don't know how well they play together, but if you write sql queries and run them from c#, it's super nice. I typically have a generic repository class that generates all the statements and it works great (I recently made the query generator able to generate inner join if I pass it filters that are not found on the table/object, based on a naming convention (any property that end in "Id" that is not the main entity Id and not a direct property, use the part before the "Id" part as the join table name) - it works great too!). I also cache certain prepared statements in a static concurrent dictionary, so I can short circuit if the it the query/insert/update has been run before for the specific object type. That way all of the string manipulation can be avoid for each query - I just use a previous statement but with different sql parameters. On every deployment the cache gets cleared because the app basically get restarted, so it won't go stale. It's very cool (at least in my mind). The only indirect rule I have is that nothing else should modify the main schema besides my migrations and not while the app is using it (using FluentMigrator code that execute on app startup). If I may ask, why don't you like C#? I totally get the php thing - I cannot stand it at all either.