Page 1 of 1
MySQL (PHPMyAdmin) - Relational Database Design/Dev Help
Posted: Thu Aug 19, 2010 9:03 am
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:
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!
Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help
Posted: Thu Aug 19, 2010 4:27 pm
by AbraCadaver
It makes sense and you should be able to do what you're trying to do. Have you tried MySQL Workbench?
Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help
Posted: Fri Aug 20, 2010 3:02 am
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.
Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help
Posted: Fri Aug 20, 2010 4:46 am
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:
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.

Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help
Posted: Fri Aug 20, 2010 6:02 am
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?
Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help
Posted: Fri Aug 20, 2010 10:20 am
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.
Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help
Posted: Mon Aug 23, 2010 3:19 am
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:
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?
Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help
Posted: Mon Aug 23, 2010 11:07 am
by AbraCadaver
What's the SQL INSERT that you are trying?
Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help
Posted: Mon Aug 23, 2010 3:37 pm
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

Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help
Posted: Mon Aug 23, 2010 4:44 pm
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
Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help
Posted: Mon Aug 23, 2010 4:53 pm
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.
Re: MySQL (PHPMyAdmin) - Relational Database Design/Dev Help
Posted: Tue Aug 24, 2010 3:08 am
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.