Geek Answers Handbook

Guid as a primary key?

I have a mysql database that has 3 tables that need to be joined. I get small databases that should load this mysql database, adding new data as it gets. The problem I have is the smaller dbs that I get are generated by an external application and are not really meant to be shared. Therefore, when I use the smaller database schema, I cannot understand how all the records from the three tables belong to each other.

I was thinking of inserting the guide as the primary key that I can add to the tables, and insert when I insert all the new data.
However, I cannot use the char field (used to store guid) as a key. Is this a real problem or is it using the char field, knowing that it will always be a guide, a sufficient solution? Can someone recommend a better approach?

thanks

+4
source share
3 answers

MySQL does not provide a GUID / UUID type, so you will need to generate a key in the code that you use to insert rows into the database. A char(32) or char(36) (if they include hyphens) is the best data type for storing the GUID instead of the actual GUID data type.

+6
source

Sorry that I am not 100% familiar with MYSQL in SQL Express, there is a unique identifier type that you can set for a column that really is a GUID. You can even set it to an auto-number so that it selects random ones.

My boss at work is HATES GUIDS, although we work a lot with autonomous / online systems, so he came up with another system in which each feed database is assigned an identifier (called DEPT), and every time he inserts a table from the master of the smaller ones, it writes its DEPT to a separate Integer column, so it's easy to sort it.

To implement this, you must make a second key (so that each table imports in a table with two keys).

Example:

 PrimaryKey1 DEPT Name 1 0 Slink 2 0 Fink 3 0 Werd 1 1 Slammer 2 1 Blam 1 2 Werrr 2 2 Soda
+1
source

Some DBs, such as ms sql server, provide a guid data type, I'm not sure about mysql.

In general, the problem is not related to char or varchar as the primary key, if they are not too long. Usually integers are preferable because they are slightly faster, but it depends on how important this is to you.

Effectively, you can also use a composite primary key. One component may be your initial primary key, which is unique in only one db. The second component can be a database number if you can assign a unique number to each database.

You can also use the following scheme:

 int newId = dbNumber * 10000 + iDInSmallDb;

So the last 4 digits are the original db, the other digits are the db number.

0
source

More articles:


All Articles