SQL id logic ~ Thoughts

[Remenission]

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 September 9, 2016 by Remenission

[Jacques1]

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 September 9, 2016 by Jacques1

[Remenission]

Jacques1

 

I'm going to try and make some major changes to shift out of using global id's and make more tables for different things as you suggested. Thank you!