Debate on which id to use as reference when quering a one to many table
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?