[SQL] Way of Relating any two Tables?

[WWGD]

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?

 

[jedishrfu]

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.

 

[jim mcnamara]

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.

 

[jedishrfu]

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.

 

[WWGD]

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?

 

[Fooality]

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 .

 

[jim mcnamara]

but also to be able to do a query on any pair of tables separately

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??

 

[jedishrfu]

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.

 

[WWGD]

Thanks all, I am just new to the topic and trying to explore. Thanks for your help, patience.