4

So i have to deal with the following scenario:

I have a table , users , which holds all of the users of my application . Each user has an email which is unique and thus could be the primary key of said table .

Let's now say i have a second table that associates some data with a user . What pops into my mind is to have that second table hold an email column which i can then use to associate a row with a user .Is this approach bad in terms of memory? Will MySQL store said email repeatedly for each extra table that holds an email column to reference users table? Should i instead just use an auto-increment id for each user and use that for the whole reference thing since an integer is way lighter than a string big enough to hold an email?

3
  • 9
    Memory use would be rather low on my list of concerns about using email as the primary key. I'd be much more concerned about what happens when some user wants to update their email address. Sure, you could define all your tables to have on update cascade foreign keys but that gets rather expensive when you have things like audit and log tables. And that tends to be one of those test cases that gets overlooked when you're adding new tables where you might have set the foreign key up incorrectly. – Justin Cave Apr 18 at 11:13
  • 5
    "Each user has an email which is unique and thus could be the primary key of said table." Primary keys must be unique and unchanging. If a user wants to change their email address you've got a problem. Also, to be "unique" emails must be normalized. FOO@EXAMPLE.COM is the same email as foo@example.com, but they are different strings. – Schwern Apr 18 at 23:23
  • @Schwern they are not though. Some email providers might treat them as the same, but it's not a given (similar case with + and .). Only the domain part is case-insensitive. – Dan M. Apr 19 at 12:44
6

Yes MySql and all other rdms will store the complete email as varchar and reserve space for the number of bytes it needs or the maximal size depending on the rdms.

Integer with up to 8 bytes for a big integer will only use these bytes, and are so faster when referencing.

In terms of speed you use INTEGER, and consider other like varchar(36) for uuids when the need arises, for example different servers have to save data in the same table.

with emails a unique and so be indexed for referencing, you should take the extra mile and use Integer if you expect you will have big tables.

0
6

If there is one email per user, add a column to the users table.

In general 1:1 mappings are not a good idea between tables; simply combine the tables.

UUIDs are a bad idea (for multiple reasons) unless you must generate unique identifiers in some distributed way.

A VARCHAR is not necessarily 'bad'; it may even save space over adding an otherwise unnecessary auto_increment.

A pet peeve is when someone uses a 4-byte INT (auto_increment) for country_code instead of a standard 2-letter country_code.

6
  • 2
    Could you expand on why UUIDs are a bad idea? – Schwern Apr 18 at 23:21
  • 3
    @Schwern Fragmentation of index, page splits during inserts, cache/page locality (relative to other data queried at the same time). A surrogate key is best if it is monotonically increasing, which is why auto increment is good and random is bad. Obviously if a natural key is likely to be queried that can be good as an index even if it is random. – Charlieface Apr 19 at 0:23
  • And more: mysql.rjweb.org/doc.php/uuid – Rick James Apr 19 at 5:30
  • @RickJames This seems to be mostly about storing it as varchar(36) which is a silly MySQLism. "With these new features in MySQL 8.0.0 (Sep. 2016; 8.0.11 GA in Apr. 2018), this blog is rendered mostly useless... The UUID stored as a VARBINARY(16) can be indexed using functional indexes.". – Schwern Apr 19 at 18:16
  • @Charlieface To be clear, are we talking about varchar(36) UUIDs or varbinary(16) UUIDs? And specific to MySQL or in general? For example, Postgres has a UUID type. – Schwern Apr 19 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.