SQL multiplicity many to many insertion process

Question

my question it seems to be pretty simple but unfortunately i couldn't find any satisfactory answer for this.

Please take a look on the following example:

Table Author define a author, Document has at least one author and table Authors group all authors that a document has.

My question is: Each time that i insert a document i need to verify if my group of authors exists already on table Authors.

What is the best way to do this? I suppose that in first place i will need to verify if this group already exist and then, i should get that id (if already exist) or generate a new record on Authors (if group doesn't exist).

Question:

Is this the correct logic process that should occur on tables that has a multiplicity of many to many?

How can i check if a group of values already exist on table Authors??

There is something like this select * from Authors where a_id IN(1,2,3) but in a exclusive way. :S

Any help would be great.

Thanks

score 1 · Answer 1 · answered Nov 16 '15 at 14:48

I would rather go with a solution with three tables:

Author
Document
rel_author_document

And rel_author_document will have a structure like:

author_fk
document_fk

You don't need to add a new group, but just to associate authors to a document.

In rel_author_document you can even add additional columns like role, if you need something like that.

With this approach you can have two documents with the same group of authors, but this won't kill you performances.

In case your question is for a homework assignment and you can't change table structure then:

Query the Authors table to see if you have a group of Author with the same number of author_id in the where condition, something like:

select count(1) cnt from Authors where a_id IN(1,2,3)
If cnt is different from the number of author_ids, then insert a new group

If cnt is equal then get the id of that group of Authors:

  select ID 
    from Authors 
   where a_id = 1
      or a_id = 2
      or a_id = 3   
group by 1
  having count(1) = 3

yes, Authors is already the third table, so you suggest to alter this table to have a document foreign key. Ok but in that way i will have authors records * document records. I was thinking about how could i have groups of authors and associate them to the document if it already exist. Probably is not a good pratice since that i would have to check if that group of authors already exist. — ePascoal, Nov 16 '15 at 15:00
a reason to have a single ID for a group of authors would be performances in case you had so many groups that your query become slow, but probably you will never need that and the logic to avoid duplicated groups is "complicated" and not worth it at this point. Better replicate the author_id each time for each document written that overcomplicate your code — mucio, Nov 17 '15 at 14:45

score 0 · Answer 2 · answered Nov 16 '15 at 14:34

If its a "many-to-many" relationship I believe the table structure you are looking for is:

Author ( a_id, name, birth_date, domain)

Document ( d_id, abstract, year)

Auth_Doc ( a_id, d_id )

In terms of checking if there is already a group of authors, it depends n how you're displaying your data. If I was uploading a document and I wanted to check if a set of authors exist, I would do the following:

Say your data is stored as:

a_id | name

1 | john, jack, bob

SELECT * FROM Author WHERE name like '%john%'

I hope this helps,

Sohail

SQL multiplicity many to many insertion process

2 Answers2