guid as primary key?

Question

Hello, I have a mysql database which has 3 tables that have to be joined together. I receive smaller databases that must feed this mysql database, appending the new data as I get it. The problem I have is the smaller dbs that I get are generated by an outside application and are not really meant to be used all together. Therefore, when I utilize the schema of the smaller database, I have no way to know how they all the records from the 3 tables belong together.

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

Thanks

Answer 1

I'm sorry I'm not 100% familiar with MYSQL, in SQL Express, there is a Unique Identifier type you can set a column to which is really a GUID. You can even set it to auto-number itself so it picks random ones.

My boss at work HATES GUIDS though, and we work with offline/online systems a lot, so he came up with another system in which each feeding database is assigned an ID (called DEPT), and whenever he inserts into the master table from one of the smaller ones, he writes its DEPT into a seperate Integer column so its easilly sortable.

---

To implement this, you'd make a second key (making each table the import has to be performed on a dual-key table).

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

Answer 2

Some DB like ms sql server provide a guid data type, I am not sure about mysql.

In general the is no problem with char or varchar as primary key unless they are too long. Normally integers are preferred because they are a bit faster but it depends if this matters for you much.

Effectively you could also use a composite primary key. One component could be your original primary key which is unique in one db only. The second component could be the number of the database, if you are able to assign a unique number to each database.

You could 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.

Answer 3

MySQL does not provide a GUID/UUID type, so you would need to generate the key in the code you're using to insert rows into the DB. A char(32) or char(36) (if including hyphens) is the best data type to store a GUID in lieu of an actual GUID data type.