close ad
 
Important WebAssist Announcement
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Relational Table or something else?

Thread began 10/17/2009 10:42 pm by troyd | Last modified 10/22/2009 1:26 pm by troyd | 5043 views | 16 replies |

troyd

Relational Table or something else?

I wondered if someone could point me in the right direction.

I have a site created in DataAssist for a dog breeder. They list breeding references, puppies and numerous other related records. I'm needing to connect some of the data from different tables. I'm assuming this is Relational Tables. But I'm still reading on this subject. As it relates to DA and any other WA product, what would be my best approach?

Here's a simplified scenario of what I already have.

1 database with 3 tables;
Table 1= Breeder males (tblMales)
Table 2= Breeder females (tblFemales)
Table 3= Puppy birth records (tblPuppies)

I then have 3 display pages on the site with repeat regions, showing all records for the subject. This is all working fine.

Now I need to create a 4th page that shows breeding/mating schedules.
Male1 is paired with Female2, bred on such and such date, expected delivery on an other date and so on.

And ultimately, join the puppies to their parents.

I have an admin section created for the user to Insert, Update and Delete all current records above.

How do I join them so that they can list one male from the males table and one female from the females table and show it on the "Breeding Schedule" page?
Since all the breeders have been entered and include 1 photo, it would be great if I could setup a simple table that includes the name or number for a male and a female and then joins them on that page while including their current photos from the other tables.

I'm guessing I would use Dynamic Drop-downs to dynamically generate a list on the Insert page for selecting a male and female that already exist in the database. But I'm confused on getting started. I read the area in the DA Help on the subject of creating a Relational table, but I got lost.

I don't mind doing my studying, but I just need pointed in the right direction. I don't find anything in the DA Wizard for including such a behavior.

Thanks in advance,
TroyD

Sign in to reply to this post

Dave BuchholzBeta Tester

Troy,

I would suggest you need to create two more tables, the first one which would be managed by a normal DataAssist form like so:

DROP TABLE IF EXISTS `tbl_breedingschedule`;
CREATE TABLE `tbl_breedingschedule` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
`maleID` bigint(20) unsigned DEFAULT NULL COMMENT 'Foreign Key tblMales',
`bitchID` bigint(20) unsigned DEFAULT NULL COMMENT 'Foreign Key tblFemales',
`dateMated` date DEFAULT NULL COMMENT 'Date Mated use <?php echo date("Y-m-d",time()); ?> or a calendar popup',
`dateDue` date DEFAULT NULL COMMENT 'Date Due use <?php echo date("Y-m-d",time()); ?> or a calendar popup',
PRIMARY KEY (`id`),
KEY `fk` (`maleID`),
KEY `fk` (`femaleID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8



and the second one which would be managed by the Manage Relational Table Server Behaviour which would look like so:

DROP TABLE IF EXISTS `tbl_breedingspuppies`;
CREATE TABLE `tbl_breedingpuppies` (
`breedingScheduleID` bigint(20) unsigned DEFAULT NULL COMMENT 'Foreign Key tbl_breedingschedule',
`puppiesID` bigint(20) unsigned DEFAULT NULL COMMENT 'Foreign Key tblPuppies',
PRIMARY KEY (`breedingScheduleID`,`puppiesID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8



Does that help ?

Sign in to reply to this post

troyd

Dave,

This helps a lot and thanks for taking the time to do that. However, it's something I'm still learning so I believe these two tables are a bit foreign to me until I figure out what to do with them.
Also, I got an error while trying to create the first table. The second table created just fine.
Here's my error in phpMyAdmin.

Error

SQL query:

CREATE TABLE `tbl_breedingschedule` (
`id` bigint( 20 ) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
`maleID` bigint( 20 ) unsigned DEFAULT NULL COMMENT 'Foreign Key tblMales',
`bitchID` bigint( 20 ) unsigned DEFAULT NULL COMMENT 'Foreign Key tblFemales',
`dateMated` date DEFAULT NULL COMMENT 'Date Mated use <?php echo date("Y-m-d",time()); ?> or a calendar popup',
`dateDue` date DEFAULT NULL COMMENT 'Date Due use <?php echo date("Y-m-d",time()); ?> or a calendar popup',
PRIMARY KEY ( `id` ) ,
KEY `fk` ( `maleID` ) ,
KEY `fk` ( `femaleID` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8

MySQL said: Documentation
#1072 - Key column 'femaleID' doesn't exist in table



Maybe I did something wrong when creating it. I just copied and pasted your code into the SQL.

A couple of questions. Will these tables work with the three I already have (once I update the column names to match), or is one of them a suggested replacement for the two I have for Male and Female?

I still need to understand the foreign key. Do I need to create a new column in my current tables to include a foreign key. And if so, how do I get them to match. I'm totally lost here. Once I do something it clicks in my head but I've yet to find a real basic example of this. I have looked through all my WA solutions such as PowerStore but I don't see anything in there.

Lastly, what do the double underlines mean in the table view of phpMyAdmin? For example, the tbl_breedingschule table has field breedingSchuduleID and puppiesID. They are underlined and then have a dotted underline. What's that telling me?

Thanks for the help. I'll continue reading in the meantime.

TroyD

Sign in to reply to this post

Office Guy-172461

Designing a Database

Don't want to but-in but maybe an image would help while you're waiting for Dave to get back to you:
161/

Sign in to reply to this post

troyd

Thanks for the image. That does help me because I can now refer to the mapping as I learn and see what I am trying to accomplish.

So a FK can be anything that would be unique in all records? For example, the current tables I have working now all have a MaleID, FemaleID and a PuppyID in their tables. As Dave made use of in the two table examples. So those current columns would act as the FK?

But how do I get the tbl_breedingschedule to insert the number from the male and female table? Do I use Dynamic Dropdowns on the insert form?
And if the MaleID is updated in the tblMale will it be updated in the tbl_breedingschedule? Do I fix the update page to update both tables?

TroyD

Sign in to reply to this post

troyd

Dave,

I think I might have figured out the first error. I noticed that the bitchID was changed to femaleID down the list.

But now it gives me an error of duplicate key name "fk"

So I'm guessing you meant for me to replace some of this stuff with my names and I just don't know enough yet to see that. Sorry for the lack of experience here.

TroyD

Sign in to reply to this post

Dave BuchholzBeta Tester

Troy,

my apologies I dashed these off quick this morning and only scanned them for errors without actually trying it my self, this version will work

CREATE TABLE `tbl_breedingschedule` (
`id` bigint( 20 ) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
`maleID` bigint( 20 ) unsigned DEFAULT NULL COMMENT 'Foreign Key tblMales',
`bitchID` bigint( 20 ) unsigned DEFAULT NULL COMMENT 'Foreign Key tblFemales',
`dateMated` date DEFAULT NULL COMMENT 'Date Mated use <?php echo date("Y-m-d",time()); ?> or a calendar popup',
`dateDue` date DEFAULT NULL COMMENT 'Date Due use <?php echo date("Y-m-d",time()); ?> or a calendar popup',
PRIMARY KEY ( `id` ) ,
KEY `maleID` ( `maleID` ) ,
KEY `bitchID` ( `bitchID` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8



A foreign key or fk is used to link tables together so in the example maleID would contain the ID value of the male dog taken tblMales and therefore becomes a foreign key in this table.

Yes the tables I gave you will work with the three tables you already have you just need to be able to link them, the dotted underlines in phpmyadmin so that there is a comment for that field.

Being able to write and understand sql statements is a required skill in designing and managing database driven applications, can I suggest that you get a copy of Sams Teach Yourself SQL in 10 Minutes which will teach you the core concepts that you need very quickly.

Sign in to reply to this post

troyd

Thanks Dave, That worked.
And yes, I would love to get that book. I have the PHP version but didn't find the SQL version at Border's. I'll order it today.

Another question. I'm inserting dummy records for now, using phpMyAdmin until I create the proper insert pages for these new tables.

Within the tbl_breedingpuppies table, the breedingScheduleID refers to what FK in the tbl_breedingschedule? I don't see a breedingScheduleID in that table. Do I use the primary ID?

TroyD

Sign in to reply to this post

Dave BuchholzBeta Tester

Originally Said By: troyd
  Within the tbl_breedingpuppies table, the breedingScheduleID refers to what FK in the tbl_breedingschedule? I don't see a breedingScheduleID in that table. Do I use the primary ID?  



Troy,

yes you would use the id from tbl_breedingschedule as the value for breedingScheduleID in tbl_breedingspuppies

Sign in to reply to this post

troyd

  Troy,

yes you would use the id from tbl_breedingschedule as the value for breedingScheduleID in tbl_breedingspuppies  



OK. I'm probably over thinking this but I don't want to assume anything.
Do my two main tables tblMales and tblFemales have both an id and a maleID or bitchID?
I'm guess no. But not sure.

I have "id" already on all my tables. Setup as int. auto-increment, primary.

Do I need to add the others or were you referring to the id as maleID and bitchID? Should I just rename my column name from id to maleID?

I ask because I see that tbl_breedingschedule has all, id, maleID and bitchID. Which I assume is because the last two are the FK for the other tables and this table needs it's own id for tbl_breedingpuppies to join with. Is this correct?

Maybe I should map out an image like the one that Office Guy referred me to. It might help me explain what I've done.

(Sam's MySQL in 10 Minutes, book on order).

Thanks,
Troy

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...