Denise Field Denise Field - 2 months ago 8
SQL Question

SQL Database Parent/Children which one should I auto increment

I have two tables in a SQL database:

Underwriters
Brokers


Underwriters
can have many brokers and
Brokers
can have many underwriters.

I created an intermediary table called
UnderwritersBrokers
and I have an
ID
in that table that is linked via a one to many relation to each of the other tables.

I want to auto increment the record ID each time I enter a new record. which table would I set the auto increment on, the child tables or the parent (intermediary)

I apologise for being a novice, this is all new to me and I just cant get my head around it.

Thanks in advance for clearing this up.

Answer

You can use auto_increment in all three!

In particular, you want to use auto_increment in both the Underwriters and Brokers tables. This will give you, respectively, a unique UnderwriterId for each underwriter and a unique BrokerId for each broker.

The junction table should then use these ids. The junction table can also have its own id. I would add one there, but a unique id in a junction table is less important than in the entity tables.