MySQL (PHPMyAdmin) - Relational Database Design/Dev Help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
timWebUK
Forum Contributor
Posts: 239
Joined: Thu Oct 29, 2009 6:48 am
Location: UK

MySQL (PHPMyAdmin) - Relational Database Design/Dev Help

Post by timWebUK »

Hi everyone,

I'm in the process of designing and developing a database to sit behind a web application I'm building, it's definitely the most complex I have ever built and I am struggling with the first form due to the huge amount of information being stored from it. (Huge for me anyway!)

Here is a screenshot of the first few tables so far:

Image

I was hoping someone could explain to me why I can't create a Primary-Foreign key relation between all the tables with intDwelling_id? It just won't let me do it.

The way i have designed it is that each project can have a dwelling, each dwelling can have multiple windows/doors/floors. So there will be maybe a dwelling with 4 floors (unique IDs for each), but they are all linked to ONE intDwelling_id.

If this doesn't make sense, please ask me to elaborate. But I'm really stuck with this design so far!
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help

Post by AbraCadaver »

It makes sense and you should be able to do what you're trying to do. Have you tried MySQL Workbench?
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
timWebUK
Forum Contributor
Posts: 239
Joined: Thu Oct 29, 2009 6:48 am
Location: UK

Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help

Post by timWebUK »

No I've never used or heard of that. Just gave it a quick look, it seems to be software dedicated to providing a visual interface for designing your database. As opposed to PhpMyAdmin's built in 'Designer'.

Edit:

Just installed it. This software is fantastic.
User avatar
timWebUK
Forum Contributor
Posts: 239
Joined: Thu Oct 29, 2009 6:48 am
Location: UK

Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help

Post by timWebUK »

Ok I still may need some assistance - I just can't get my head around these Primary and Foreign keys.

Is it possible to do the following:

1 - 1 tblproject to tbldwelling (Each project has one dwelling)
1 - * tbldwelling to tblwalltype (Each Dwelling can have several different types of wall)
1 - * tbldwelling to tblwindowtype (Each Dwelling can have several different types of window)
1 - * tbldwelling to tbldoortype (Each Dwelling can have several different types of floor)

Each type would obviously have a unique ID, but share the same dwelling ID so they are linked... can't get this to work at all.

I created this using the EER editor:

Image

Yet when I forward engineer it has added fields to my tables and alsorts, and the design still doesn't appear correctly in PhpMyAdmin. Seriously stuck here.

...MS Access seemed a lot easier for relationships all those years ago. :roll:
User avatar
timWebUK
Forum Contributor
Posts: 239
Joined: Thu Oct 29, 2009 6:48 am
Location: UK

Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help

Post by timWebUK »

Ok, as usual I have managed to resolve the issue myself (I think), which I prefer! I feel like I've achieved something.

Going to keep the thread updated anyway as a bit of resource for myself. I'll let you know and ask for help if I get stuck with the ID issue!

EDIT:

I could smell it coming...

Code: Select all

Error: Cannot add or update a child row: a foreign key constraint fails (`dbphlorumcarbon`.`tbldwelling`, CONSTRAINT `fk_tblDwelling_tblProject` FOREIGN KEY (`intProject_id`) REFERENCES `tblproject` (`intProject_id`) ON DELETE CASCADE ON UPDATE CASCADE)
Any suggestions on what I've done wrong?
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help

Post by AbraCadaver »

You can not execute an UPDATE or DELETE that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
timWebUK
Forum Contributor
Posts: 239
Joined: Thu Oct 29, 2009 6:48 am
Location: UK

Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help

Post by timWebUK »

It would seem then that I haven't created the foreign keys correctly as I thought I had. Would you be able to quickly explain how to do it MySql workbench?

I've been clicking the 1:1 and 1:* buttons (identifying relationship) on the foreign key table, then on the referenced table. But I jsut don't understand why it is behaving strangely, creating lots of new columns in my table. I just want:

intDwelling_id (Primary Key)
intProject_id (Foreign Key - referencing to tblProject).

It just do it that simply. Putting an index saying fk_tblDwelling_tblProject. And the same for my other tables. Which then causes my error to be thrown.

EDIT:

Image

This is the database I'm using, this is the current error:

Code: Select all

Error: Cannot add or update a child row: a foreign key constraint fails (`dbphlorumcarbon`.`tbldwelling`, CONSTRAINT `tbldwelling_ibfk_1` FOREIGN KEY (`intProject_id`) REFERENCES `tblproject` (`intProject_id`) ON DELETE CASCADE ON UPDATE CASCADE)
All I can think of my SQL is wrong, I'm developing this system step by step, and because I've started with tblDwelling, would that cause an error? As there would be no value to populate the FK?
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help

Post by AbraCadaver »

What's the SQL INSERT that you are trying?
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help

Post by VladSun »

timWebUK wrote:... I've started with tblDwelling, would that cause an error? As there would be no value to populate the FK?
You can't "start" with tblDwelling because there is a table to be created, filled, and referenced in first step - tblproject.

PS: BTW why do you have to put the tbl* prefix - it's clear it's a table :mrgreen:
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help

Post by Eran »

BTW why do you have to put the tbl* prefix - it's clear it's a table
Same for int{columnName} etc .. this information is already available in the table structure
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help

Post by VladSun »

pytrin wrote:
BTW why do you have to put the tbl* prefix - it's clear it's a table
Same for int{columnName} etc .. this information is already available in the table structure
Oh ... yes, I didn't notice it.
IMHO, such prefixes are useful only if they match the Hungarian notation rules:
http://en.wikipedia.org/wiki/Hungarian_ ... _Hungarian

Anyway, in an DB context it makes no sense.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
timWebUK
Forum Contributor
Posts: 239
Joined: Thu Oct 29, 2009 6:48 am
Location: UK

Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help

Post by timWebUK »

But when referring to HTML forms and PHP and JavaScript, it is pretty useful for me. Especially seeing as I have quite a few integers and floats that I wouldn't want to confuse.

Also hungarian notation doesn't have completely strict rules as to what you can and can't prefix with, for me it works and I think another developer if they took over could figure it out nicely.

P.S. I have it all working and up and running - once I started with tblProject to ensure I had a foreign key for tblDwelling.
Post Reply