0

our database contain 5+ tables

user
---------- 
user_id (PK) int NOT NULL
name varchar(50) NOT NULL

photo
--------
photo_id (PK) int NOT NULL
user_id (FK) int NOT NULL
title varchar(50) NOT NULL

comment
-------
comment_id (PK) int NOT NULL
photo_id int NOT NULL
user_id int NOT NULL
message varchar(50) NOT NULL

all primary key id's are unique id's.

all data are linked to http://domain.com/{primary_key_id}

after user visit the link with id, which is unique for all tables.

how should i implement to find what table this id belongs to?

solution 1

select user_id from user where user_id = {primary_key_id}

// if not found, then move next
select photo_id from photo where photo_id = {primary_key_id}

... continue on, until we find which table this primary key belongs to.

solution 2

  1. create object table to hold all the uniqe id and there data type
  2. create trigger on all the tables for AFTER INSERT, to create row in object table with its data type, which was inserted to a selected table
  3. when required, then do select statement to find the table name the id belongs to.

second solution will be double insert. 1 insert for row to actual table with complete data and 2 insert for inserting unique id and table name in object table, which we created on step 1. 

select type from object_table where id = {primary_key_id}

solution 3

  1. prepend table name + id = encode into new unique integer - using php
  2. decode id and get the original id with table name (even if its just as number type)

i don't know how to implement this in php, but this solution sounds better!? what are your suggestion?

Basit
  • 16,316
  • 31
  • 93
  • 154
  • Both your solutions will work. The question is more about how often you need to do the operation in question. solution 2 is probably faster but requires an extra table and also more cost at insertion time. solution 1 is simpler but requires up to 5 select statements. Btw: did you think about using unique ids with "speaking" numbers? Then you could know the original table from the number. – luksch Apr 23 '13 at 18:22
  • How do you create the links? Make your links something like `http://domain.com/page.html?pk={id}&table={table}` (When you `echo` the link add the table as an attribute like the ID). Later you can parse the url and use the table attribute as a variable in the query. Table ID's don't need to be unique in the whole DB. If I understand you correctly you are wasting a lot of usable ID numbers. – ZZ-bb Apr 23 '13 at 18:30
  • @luksch im using UUID_SHORT() to create unique id for all the tables. i prefer solution 3, but dont know how to implement encoding / decoding on it. what you think? – Basit Apr 23 '13 at 18:33
  • @ZZ-bb im trying to implement facebook api like implementation on data. – Basit Apr 23 '13 at 18:34
  • If you can change the table definition to use a string as primary key then you could just concatenate your id with a table identifier string upon row insertion. – luksch Apr 23 '13 at 19:08
  • Solution 1 is a bit silly. It's more an answer to a question "How can I select record from a set of tables not knowing the table?". 2 and 3 on the other hand indicate that you create these data links so it would be wise to create a such link that contains all the needed information about a table and ID. You can use them both at the same time and choose the better later. (2: you store the data link into DB. 3: you create it in the fly.) Without knowing where and how these links are actually used it's not sensible to recommend one or another blindly. – ZZ-bb Apr 24 '13 at 06:45

2 Answers2

0

I don't know what you mean by Facebook reference in the comments but I'll explain my comment a little further.

You don't need unique ID's across five DB tables, just one per table. You have couple of options how to create your links (you can create the links yourself can you?):

  1. using GET variables: http://domain.com/page.html?pk={id}&table={table}
  2. using plain URL: http://domain.com/{id}{table}

Depending on the syntax of the link you choose the function to parse it. You can for example use one or both of the following:

When you get the simple model working you may add encoding/decoding/hashing functions. But do you really need them? And in what level? (I have no experience in that area so I'll shut up now.)

ZZ-bb
  • 2,157
  • 1
  • 24
  • 33
  • http://developers.facebook.com/docs/reference/api/using-pictures/ scroll down to "Examples of Supported Objects" and you can see what i mean by using unique id across multiple tables and what im trying to implement. – Basit Apr 23 '13 at 19:55
0

Is it actually important to maintain uniqueness across tables?

  • If no, just implement the solution 3 if you can (e.g. using URL encoding).
  • If yes, you'll need the "parent" table in any case, so the DBMS can enforce the uniqueness.
    • You can still try to implement the solution 3 on top of that,
    • or add a type discriminator1 there and you'll be able to (quickly) know which table is referenced for any given ID.

1 Take a look at the lower part of this answer. This is in fact a form of inheritance.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167