Debate on which id to use as reference when quering a one to many table I have a database that has a One to Many relationship. A few changes are in order and now when I query the output I need to provide just one ID field. The output contains all iterations of the one to many relationship so that if the parent has many child, the parent and each child will show up as one row in the output.

I'm debating how to handle giving the output an id, (or reference number) and I see that I have three options.

Option one

I can make a new table, or alter one I already have, which would give each Parent/Child pair it's own unique ID. 

Option Two

Since it is a one to many relationship I could always just reference the child id, since that would be unique in it's own right.

Option Three

I can 'generate' an id on the fly which would Concat the parentid, and the childid, padding the childid with 4 leading zeros for later expansion.
Option One, Make a new table referencing the id of the Parent/Child Relationship

Pros


If you need to obfuscate the data, or generate a GUID, or a custom id
for an external department working with the data, this might be a valid option to consider.


Cons


It's not useful for any other situation that this first person can think of.




Option Two, Use the Child Id

Pros


The number is already there.
The parent id is assumed to be in the same table as the child id which is convienent
This is probably the simplest way to go.


Cons


Unlike option one, whoever receives the query will then know the child id. In most cases it's fine. But could potentially be a security risk?

Examples:




Option Three, Use the existing Parent and Child ID to generate a new one

Pros


You get an id number that has the data for the Parent and the Child
A query can generate this number on the fly, no need to store it.


Cons


Security wise you are giving away two id numbers
You can potentially reach a limit. Padding with zeros means that once there are enough entries in the database, which may or may not ever happen, you will hit the upper limit on the number you generate and thus potentially risk creating duplicate ids

Example

Parent id = [1 to 300]
Child id = [1 to 3000]
Concat = Parentid + string formated child id that always shoes 4 numerals (0000)

10001 First possible entry [1] + [000] + [1]
3003000 Last possible entry [300] + [3000]



If in the future the child has 10023 as an id you might get

    110023
    is this
    [1] + [10023]
    or is it
    [11] + [0023]


It may not matter if the result is always exported from the database, but if you need to return data to the database based on using these ids, then it might cause issues.



Interesting similar questions

Which is better: identity columns or generated uinique id values?