Joe Scotto Joe Scotto - 1 month ago 11
SQL Question

When using foreign keys do you always have to join?

I'm new to working with relational databases and have a sound understanding of how to query and join tables. My question today is a bit of a 2 part question because I don't really know how else to phrase it.


  1. Generally, is it a good idea to break up tables with a lot of columns into multiple tables of "grouped data". From a query point, it should save time when certain data is not needed and it also makes it more readable.



For example, normally I would combine all of the following columns into one table and then query for all the data even if just using a few columns. I'm currently experimenting with the following database structure, the tables would all link via the
uid
column. :

uploads


  • id

  • name

  • title

  • description

  • email

  • date

  • uid



uploads_exif (everything but uid is optional)


  • id

  • aperture

  • iso

  • shutter_speed

  • camera

  • uid



uploads_social (everything but uid is optional)


  • id

  • facebook

  • twitter

  • instagram

  • flickr

  • url

  • uid


    1. If following the structure listed above, in order to query all the data am I required to use SQL JOINS or is there a way where I can simply query all tables. For example, if I need to get uploads, uploads_exif, and uploads_social in a single query is it possible to use another function to simply query all of them without using a JOIN or is using a JOIN the correct way to go about doing this.




Any help would be greatly appriciated, thanks!

Answer

First you should try to study Third Normal Form.

The number the columns isnt the main factor, the important is try to avoid repetition so maintenance become easier.

For example if you have a table

employee: [id, first_name, last_name]

You wont need to split it that into two tables, ok this is extreme but you get the idea.

employee_first: [id, first_name]
employee_last: [id, last_name]

The other case is avoid repeat data.

if you have a field favorite_fruit instead of using a text field you create a fk table and save integers.

   favorite_fruit      favorite_fruit       id  fruit
   orange                 1                  1  orange                 
   orange                 1                  2  apple
   apple          ==>     2                  3  pineapple
   pineapple              3 

Save space and is easy to edit, for example if you want change orage to orange juice only need replace one.

Not sure what is your case, but for example if you have different type of upload you can have a master table upload with type_id and each type can have a separated table for details.

    UPLOAD               |     table: [UPLOAD TYPE1]                     
 upload_id  type_id      | upload_id  [fields unique for type1]  
    xxxx      1      ==> |   xxxxx    ooooooo                 
    yyyy      2          |===================================== 
                         |     table: [UPLOAD TYPE2]
                         | upload_id [fields unique for type2]
                         | yyyyy     oooooooo

Another example is employee adress.

istead of add multiple field for building, street, zip code you create three tables

   employee: employee_id
   adress:  address_id, building, street, zip code
   employee_adress: employee_id, adress_id.

That way one employee can have multiple address just adding a separated entity address and assign to employee using employee_adress