Jump to content

SQL id logic ~ Thoughts


Go to solution Solved by Jacques1,

Recommended Posts

For the business I work for I have a platform setup that handles everything from messages to photos to blogs etc.

 

I have two tables that show id releations:

CREATE TABLE IF NOT EXISTS Relations (
	sender int(9),
	receiver int(9),
	relation tinyINT NOT NULL DEFAULT 0 /* -1=Delete, 0=[Add]Pending, 1=related, 2=Blocked */
);

CREATE TABLE IF NOT EXISTS Rating (
	owner int(9),
	id int(9),
	rating tinyint(1) /* - 0 through 9 rating - */
);

With these tables I can relate any ID to any ID:

 

With relations table I can relate a user to any other user, or to any other group ETC...

With the rating table I can have a user rate any group, page, blog, message, photo, album etc...

 

Because of this I have globally unique id's.

 

No message will have the same ID as a blog, or a user, or a group, or a page.

Currently I manage this in this manner:

 

ID's: (I added commas for readability)

 

(1)00,000,000 - users

(2)00,000,000 - groups

(3)00,000,000 - albums

(4)00,000,000 - photos

(5)00,000,000 - blogs

(6)00,000,000 - msg

 

As you can see, each object is represented by the leading digit. So currently,

to make sure they are all distinct, before anything is inserted I am doing a full join on all of these tables for the column 'id'.

If any results come back it generates a new 'id' to test for uniqueness.

 

I feel like the way I have this setup could probably be done a lot cleaner and smoother. I have tried googling solutions, but

I haven't had much luck because my scenario is kind of complex: not to say others haven't encountered this issue.

 

Anyways, if anyone has any thoughts or opinions I would greatly appreciate it. Thank you guys ~ Remey

Edited by Remenission
Link to post
Share on other sites
  • Solution

First off, I would not use a global pool of IDs, because this is complex and makes it very difficult (if not impossible) to set up a proper database layout with foreign keys. Right now, your ratings don't point to any real entities, you're just storing arbitrary numbers. If I wanted to “rate” the ID −456, I could do that, which is obviously a problem.

 

A far better approach would be to have plain old per-table IDs and different tables for the different ratings. Even a single table with a type column (i. e. no referential integrity) is better.

 

If you absolutely must have global IDs, use random UUIDs (MySQL even has a function for that). Your uniqueness checks don't work.

Edited by Jacques1
Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.