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