• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Table design and query question

 
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,

I'm quite new to working with databases, so I'm hoping you can assist me with my question.

I have a database where I want to allow users to store a list of comic books. I need a way to represent the list of comics that belong to a specific user.

My idea would be to have the following tables: user, comic, and a user_comic table. The user_comic table would be comprised of the primary keys of the user and comic tables. Would this be a proper table design or is there a different/better way?

Then to see what comics a user has would require a query involving all 3 tables?

I'm not looking for someone to provide the SQL statement. Just some comments that may push me in the right direction.

Thanks!
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The question just comes in the cardinality of the relationship. Is it one-to-one, is it Many to one, or is it many to many. The way you have it is called a join table which is used for Many to many and sometimes one to one. But it looks more like you have a many to one relationship, meaning one user can have many comics, but one comic can only belong to one user. That is Many to one, and will mean that the comic table will have a user_id field in it as a foreign key.

Mark
 
Ranch Hand
Posts: 686
Netbeans IDE Chrome Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
looks good
 
Mark Spritzler
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Vyasu Sanzgiri:
looks good



What looks good? We don't know what his cardinality is supposed to be, and if it is One-to-Many, then his design is not looking good, it has an extra table that he doesn't need.

Mark
 
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If one book belongs to one user why cant you have an extra column in comic books table that will store the owne of the book ?
 
Kerry Friesen
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Mark Spritzler:


What looks good? We don't know what his cardinality is supposed to be, and if it is One-to-Many, then his design is not looking good, it has an extra table that he doesn't need.

Mark




Hi Mark,
Thanks for your reply. One user can have many comics, but only one comic can belong to a user. So, my table design is incorrect. I will only require the foreign key in the comic table.

Cheers,
Kerry
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic