sql - Inserting Into and Maintaining Many-to-Many Tables -

-

sqlite3 user.

i have read thru numerous books on relational dbs , sql , not 1 shows how maintain linking tables many-to-many relationships. went through book went details of select , joins examples, glosses on same when many-to-many relationships covered. author showed pseudo code table, no data, , pseudo code query--wtf? missing something, has become quite maddening.

anyways, have table [people] 3 columns: pid (primary), name , age. table [groups] 3 columns: gid (primary), groupname , years. since people can belong multiple groups , groups can have multiple people, set linking table called [peoplegroups] 2 columns: pid, , gid both of come respective tables.

so ,how efficiently data linking table when inserting on others , how data out using linking table?

example: want insert "jane" [people] , make member of group gid 2, "bowlers" , update linking table {peoplegroups] @ same time. later want go , pull out list of of bowlers or groups person part of.

share|improve question
    
when need new membership, add new record. problem that? – cl. apr 24 '14 @ 7:02
up vote 0 down vote accepted

if don't use primary , foreign keys (which should!) think may need consider using triggers in design well? if have specific set of rules (e.g. if want create jane id = 1 , choose existing group 2, after insert jane people automatically create entry pair personid=1,groupid=2 in table peoplegroups. can create views specific selects see data want, example if want query show peoples names , groups names create view 'peopleview':

select p.personname, g.groupname people p inner join peoplegroup pg on p.personid = pg.personid inner join group g on g.groupid = pg.groupid

then can query 'peopleview' saying

select * peopleview groupname = 'bowlers'
share|improve answer
    
thank you. guess need master joins bit more. – raw_input apr 24 '14 @ 22:23
    
so took info provided here , studied , practised joins--"until fingers bled"--and came different db created: -- boldselect deptname, empname empdept join department on empdept.[deptid] = department.[deptid] join employee on employee.empid = empdept.[empid] order empname;**bold** help. – raw_input apr 26 '14 @ 2:33
up vote 0 down vote

when inserting new data tables mentioned, "linking" table referring needs contain both primary keys other tables foreign keys. [people] tables (pid) , [groups] table (gid) should both foreign keys in [peoplegroups] table. in order create new "link" in [peoplegroups] record has exist in [people] table [groups] table before try , create link in [peoplegroups] table. hope helps

share|improve answer
    
thanks reply. using primary , foreign keys. did figure out not need update linking table @ same time other tables. extracting data three, taking answer below , going practise mastering joins, seems major "malfunction." – raw_input apr 24 '14 @ 22:26

your answer

 
discard

posting answer, agree privacy policy , terms of service.

not answer you're looking for? browse other questions tagged or ask own question.

Comments

Post a Comment