reference other tables

michaewlewis

is it possible to make a reference to table "computer_bays" within table "computer_main" and make the two tables work together?
I want to create a series of tables for easier use. It may look something like this:

computer_main
	name CHAR(20),
	case_type CHAR(50),
	motherboard CHAR(50),
	processor CHAR(50),
	bays [reference table computer_bays],
	ram INT,
	slots [reference table computer_slots],

computer_bays
	IntBay1 CHAR(20),
	IntBay2 CHAR(20),
	ExtBay1 CHAR(20),
	etc.

computer_slots
	AGP1 CHAR(20),
	AGP2 CHAR(20),
	PCI1 CHAR(20),
	PCI2 CHAR(20),

AGP1 CHAR(20),
AGP2 CHAR(20),etc.

How can I tell the "bays" field to get its values from the computer_bays table? Is it even possible? And of course, am I completely lost, going down the wrong path, headed for a steep cliff? I am trying to figure out all the data normalization stuff and not too sure about it.
Any help will be appreciated!

bogu

SELECT * FROM computer_main cm
LEFT JOIN computer_bays cb ON cm.bays = cb.IntBay1

U can read more about this here.

michaewlewis

What is the "cm" and the "cb" all about? You can assign nicknames to columns? Where can I read more about that?
And I am assuming I will have to use the join feature for every entry. Correct?
Thanks for the help

ahundiak

Lots of possibilities. Need to read some basic database normalization tutorials.

You could do:
computer_main
..computer_main_id - unique number identifying a given computer configuration, typically auto_increment

computer_bays
..computer_bay_id
..computer_main_id - ties this bay to a particular computer
..type (internal, external etc)
..number (1,2,3 etc)

With this approach you can have as many bays as you need for a given computer configuration.

michaewlewis

Ya, I've tried to read several normalization tutorials. None of them that I have looked at give very good examples and I'm the kind of guy that needs a good picture to get it. 🙂
I'm pretty sure if I saw a good database design I could recreate my own, but I haven't seen anything like that published. 🙁
Know of a good tutorial like that?

bogu

michaewlewis wrote:
What is the "cm" and the "cb" all about? You can assign nicknames to columns? Where can I read more about that?
And I am assuming I will have to use the join feature for every entry. Correct?
Thanks for the help

cm is am alias of computer_main ...

ahundiak

Have you checked the articles on the main phpbuilder site? I think they are pretty good.