Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

[SQL] Way of Relating any two Tables?

Tags:
  1. Mar 27, 2015 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Hi all,
    Say we have a collection T_1, T_2,...., T_n of tables. I would like for any pair to be related. Would it

    work if I where to select any field, say the primary key PKT_1 for T_1 and insert it (albeit artificially) meaning

    include it as an attribute in every other table? If we did this, any two tables would then have PKT_1 in common.

    Would this work?
     
  2. jcsd
  3. Mar 27, 2015 #2

    jedishrfu

    Staff: Mentor

    Tables are usually combined via the select statement in the where clause. You could use the primary key or T1 as a column in the other tables to bind them together.

    As an example, say the primary key was a customer-id and T1 contained customer name and email. T2 could then be used to collect the items bought by a customer and the T2 table would have a customer-id column as well. In T1, the customer-id would have a uniqueness constraint as no two customers can have the same customer-id.
     
  4. Mar 28, 2015 #3

    jim mcnamara

    User Avatar

    Staff: Mentor

    You are describing a foreign key. If you have n tables as you describe and they originally did not have that foreign key, then you are correct ;you should rethink your data model.

    A primary key for a table should be unique, foreign keys are not necessarily unique. It sounds like you might want to learn about database normalization. Why? When you have the situation you appear to describe, the database design often has lots of problems.

    http://en.wikipedia.org/wiki/Database_normalization

    And if I am reading this correctly, then: no you do not want every table "pairable" with every other table.
    Normalized data sets allow pairing by using a foreign key to get to a second table, that table has a key to the third, and so on. Joining tables (in my rather crummy explanation) is how you assemble the relationships you need. IF you are correct and need paring then your design has issues.
     
    Last edited: Mar 28, 2015
  5. Mar 28, 2015 #4

    jedishrfu

    Staff: Mentor

    One such schema design is the star schema where there is a central table of your collected data and keys that connect it to much smaller attribute tables.

    As an example, the central table might contain all magazine subscriptions with one key tied to the customer table, another key tied to the magazine table and yet another tied to the subscription plan in force others columns might represent start of subscription and duration.

    Various queries could be made to count or list subscribers to a given magazine or group of magazines.
     
  6. Mar 28, 2015 #5

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thanks to all for your replies:
    I see, so if I were to do a query on a number k of tables, (k is greater-or-equal to two), then all I need to get a "useful output" (meaning not something like a Cartesian product) is that, in the ERD (Entity-Relation Diagram) associated to the selected tables, there is a path joining any two tables , even though, as JimMcNamara said, not every pair of tables is related?

    But my question was not about just relating n tables, but also to be able to do a query on any pair of tables separately. This is also why I was thinking of inserting the primary key of table 1 on all others. Is this a desirable thing, to be able to do a query on any pair of tables , i.e., on the join of any pair of tables?
     
    Last edited: Mar 28, 2015
  7. Mar 28, 2015 #6
    I just think of it in terms of references/pointers, like in a programming languages. If a table "Orders" has a reference column to the primary key of table "Customers", than for each Customer, there can be many Orders. (Its like if you had a Customer object with a list that's part of it pointing to Orders.) This is a "many-to-one" relationship between orders and customers. For a many-to-many relationship, like Customers to Products, which records which customers like which products, a separate table CustomerLikesProduct is made, with two columns, customer_id, and product_id. Putting a reference to the main table M in all the other tables is like M having a list, which can have every other object in it. Its more likely you'll have lots of references to the primary keys of other tables in all your tables.

    These represent structural links between tables in the database you are recording. Other links can be created on the fly through joins, even without any key data stored. An example would be a query that joins customers with a table of featured products, where feature product price is less than customer max willingness to spend column. You can then email each customer a list of featured products within their stated price range, but that information isn't stored in the database, its just constructed on the fly for this query. The join was on dollar amount customer.max_will_spend and featured_product.price .
     
  8. Mar 28, 2015 #7

    jim mcnamara

    User Avatar

    Staff: Mentor


    This is not what relational databases are meant to do. As a ridiculous example: relating someone's shoe size in table A to sunrise time for the timezone he lives in is not practical... You have to know ahead of time how things work, not take an a posteriori approach and say I want to connect any two tables at any time.
    You are better off with a gigantic single table, and for each of the instances where :'but also to be able to do a query on any pair of tables separately' , do a full table scan. If you can link any two tables with just a few foreign keys, then many of those table should be merged.

    And since you already made up your mind: Sure! Go for it, anything is possible, ignoring practicality.

    Creqate a large key metadata table, that has some identifiers and rowid pairs from all tables that relate them as you want. This means any time you update any table you now have to update this larger metadata table for all tables. If you have 60 base tables, then every insert into a base requires a mass update to the metadata table. Combinatorics 101: that means how many pairs of rowids get added? 60 tables with say 200 rows each. Hint: lots! If you have lots of rows in base tables your metadata table will be the size of Kansas.

    The other aspect of this approach is that if you ever have to reorg any of the base tables, the rowids are then invalid, so you have to rebuild the entire giant table.

    Bottomline: either take the metadata choice or dump everything into one large table with lots of columns. For a very tiny dataset either one can be done. Some databases limit the number of columns a table can have. What db are you going to use? MySQL??
     
    Last edited: Mar 28, 2015
  9. Mar 28, 2015 #8

    jedishrfu

    Staff: Mentor

    Here's info on star schemas that are one way of organizing data in a database:

    https://en.wikipedia.org/wiki/Star_schema

    Production databases may organize the data differently based on how its loaded into the database or how its used.
     
  10. Mar 28, 2015 #9

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thanks all, I am just new to the topic and trying to explore. Thanks for your help, patience.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: [SQL] Way of Relating any two Tables?
  1. SQL basics (Replies: 4)

  2. SQL relationships (Replies: 3)

  3. Sql join? (Replies: 4)

  4. SQL Help (Replies: 2)

Loading...