guid as primary key?

Question

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

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.

Answer 2

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