MySQL Forums
Forum List  »  General

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

Options: ReplyQuote


Subject
Written By
Posted
Requesting help in setting up table relationships
September 08, 2012 10:35PM


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.