Requesting help in setting up table relationships
Posted by:
Angus Young
Date: September 08, 2012 10:35PM
I"m currently working on a side project whereby I’ve populated a mySQL database with 5 tables. I'm not sure how to link the tables to each other via foreign keys, especially in the presence of composite primary keys.
I'd like to ask you for your help in determining the proper relationship from a SQL point of view. If I was to delete an entry in the computer_systems table, say acme_100 for example, I would want to have its entries deleted from the other tables as well. Also, I’d like to be able to select a specific configuration of the computer system (PC_name) and have the corresponding dependent fields in the other tables be displayed for selection and filtering.
But first, I need to get the dependencies between the tables correct.
I'm using myPHPadmin which permits me to manually create tables and add values. It also has a SQL terminal for me to enter sQL commands. I also have access to mySQL workbench.
I've got a pic showing snippits of each of the tables (there will be more rows when I'm done). I'm not sure how to attach it to this message though?
The 5 tables are as follows:
table: computer_system
column 1: PC_name (PK)
column 2: manufacturer
table: system_params
column 1: PC_name (PK)
column 2: CPU (PK)
column 3: algorithm (PK)
column 4: pwr_consumption
column 5: nodes_processed
In this table, pwr_consumption and nodes_processed are dependent on the unique combination of PC_name, CPU, and algorithm which collectively
I believe would be a composite primary key. PC_name would be a foreign key to the same name in the computer_system table.
table: filter_params
column 1: algorithm (PK)
column 2: filter_coeffs
OK, this is a little confusing to me, algorithms in this table needs to link to algorithms in the system_params table. I'm not sure how
to properly do this.
table: algorithm_params
column 1: algorithm (PK)
column 2: method
column 3: lines_of_code
column 4: num_variables
column 5: num_authors
same comment as previous.
table: CPUs
column 1: CPU (PK)
column 2: cache
column 3: GHz
CPU will need to link to the system_params table as well. I'm just not sure how to do this.
I'd appreciate your feedback. I'm a total noob to DB programming and am stumped at the moment.
Cheers,
Angus
Subject
Written By
Posted
Requesting help in setting up table relationships
September 08, 2012 10:35PM
September 09, 2012 03:30AM
September 09, 2012 08:44AM
September 09, 2012 11:19AM
September 09, 2012 12:17PM
September 09, 2012 10:24PM
September 10, 2012 02:18AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.