Re: binary index - Arthur Fuller - com.mysql.lists.java

7 messages in com.mysql.lists.javaRe: binary index

From	Sent On	Attachments
David Parker	06 Dec 2002 09:43
Dorel Vaida	08 Dec 2002 22:51
David Parker	09 Dec 2002 06:02
Arthur Fuller	09 Dec 2002 10:26
Udkik	10 Dec 2002 02:06
Dorel Vaida	10 Dec 2002 02:45
Arul	10 Dec 2002 03:09

Subject:	Re: binary index
From:	Arthur Fuller (art...@rogers.com)
Date:	12/09/2002 10:26:11 AM
List:	com.mysql.lists.java

There are a variety of ways to do this:

1. Use GUIDs as your PKs. GUIDs are guaranteed unique for the next century. Unfortunately I don't have any source that gens GUIDs in Linux, just in Windows. 2. Give every branch office ID a column and then make the PK BranchID+ItemID, where ItemID is autoincrement. MySQL is very smart about this: it will give you data such as:

BranchID ItemID 1 1 1 2 1 3 2 1 2 2 3 1

IMO, FKs should never comprise >1 column. So sometimes I manufacture the column from the other pair or triad and use that as the FK.

hth, Artful

No, the primary keys are not data holders - they are just pure keys. The application is a distributed content management repository, and objects created/accessed from one repository must have a unique identifier across all possible repositories in which they might become involved (through links, etc.). So, yeah, the PK is just a PK. My problem is that it is a BIG PK!

Thanks for the reference on primary key generation patterns. I'll take a look.

- DAP

-----Original Message----- From: Dorel Vaida [mailto:Dore...@ags.ro] Sent: Monday, December 09, 2002 1:52 AM To: David Parker; ja...@lists.mysql.com Subject: RE: binary index

I'm sorry, but from what you are saying, I understand that in your app even primary keys (represented by UUID pattern, by the way, is that architecture distributed or why do you use them ?) are data holders. If it is like this, than it's bad design. You never use primary keys as data holders. Just because data in a system allways change. and a primary key shouldn't be afected in any way by data changes. So change the design and let PK to be PK, no other meanings.

In the UUID mater, if you can switch to other type of id, switch if the app is not distributed. UUIDs are supposed to asure unicity across a cluster. For pros/cons regarding this pattern just download Floyd Marinescu's EJB Design patterns from www.theserverside.com and look into prim key generation patterns section. you'll find it.

If it doesn't help, I appologise. dorel

-----Original Message----- From: David Parker [mailto:da...@rbii.com] Sent: Friday, December 06, 2002 7:44 PM To: ja...@lists.mysql.com Subject: binary index

In my application everything has a UUID, a 128-bit indentifier. I want to store these as keys in my MySQL/InnoDB tables.

What I have been doing is just converting the UUIDs to 32 character hex strings, and defining the keys in the tables as char(32). Since IDs are a big component of the data, having to use a hex string almost doubles the size of my tables, in some cases.

If I use a char(16) and just populate the key field with PreparedStatement.setBytes(), the indexing, etc. seems to work fine, but the key is not human-readable. I tried using a tinyblob, but InnoDB can't use that as a primary key, apparently.

Has anybody done anything similar to this? Ideally, I would be able to store the UUID as a fixed length blob (16 bytes), and use something like a to_hex_string() function on the key when I wanted to look at things from the mysql shell....

- DAP

============================================================= David Parker - Red Bridge Interactive (401) 223-1135

"If you want sense, you'll have to make it yourself." The Dodecahedron

To request this thread, e-mail <java...@lists.mysql.com> To unsubscribe, e-mail <java-unsubscribe-dorel.vaida=ags...@lists.mysql.com>

To request this thread, e-mail <java...@lists.mysql.com> To unsubscribe, e-mail <java-unsubscribe-artful=roge...@lists.mysql.com>