SpectralDesign.Net

[!--quoteo(post=353508:date=Mar 9 2006, 10:35 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 9 2006, 10:35 PM) [snapback]353508[/snapback][/div][div class=\'quotemain\'][!--quotec--] I use Visio on Windows, it was free for me because of college. :) It doesn't exactly do traditional ER modeling, but it does have a nice setup that serves the same purpose. There's a drawing program for linux called Dia that I use to draw real ER diagrams. It doesn't have a lot of nifty features, but it makes it possible to draw a competent diagram with some effort. Also it's open source and free as in beer. [a href=\"http://www.gnome.org/projects/dia/\" target=\"_blank\"]http://www.gnome.org/projects/dia/[/a] [/quote] Excellent! This is perfect... sure it'd be nice if it could import/export SQL structure but this is the next best thing! I spent a while trying to compile it, then having to step back to install perl XML:parser, then again stepping back to install pango, then I got an other "missing" error and checked the repositories for a package of Dia, which fortunately was already there (doh!) (mental note - always check the repositories first!) Thanks again, Wickning1!

Just to make an amendment to Fenway's suggestion.... Anecdote: My first MySQL DB was a super-rudimentary 3-table DB.. one table was only there to fill a set of pull-down menus (country, province type of thing) and two more one-one tables which could have been a single table, but I wanted to save a little space since I didn't think every user would go and make a profile so I kept the membership table and profile tables independent. Now, my next project that I'm currently working on, is a large web of many-many tables (by necessity) with tables for: persons, addresses, phones, organizations, email_addresses, and more... PHEW! What a learning curve! Simply learning how to properly query the tables to draw up proper reports has been a small journey in itself, and ahead of me I'll need to learn how to perform a data migration mapping from a flat-DB (excel, yuk!) which is a looming unknown to me right now, and as well there will be a ton of little details in creating a useable interface to manage (search, edit, add, delete) records from the DB. But -- you know what? Once you know these things, you're way ahead of the game. Better to learn from the get-go than to keep building DBs that are less than best-practice standard, in my opinion.

I think it depends on what you're trying to do... Are you satisfied with what you can get from a flat-database? Will it scale to include functions that might be handy in the future? For instance, might you want to keep track of inventory levels with it down the road so that both you and your customers know if an item is sold-out? Basically, if the relation between any pair of attributes will always be a one-to-one relationship, flat DB (i.e. a single table) should be adequite. If you have any pair of attributes that could be a one-many or a many-many that's when you really need to flex your relationalal DB muscles... To get away from your stock thing (because I can't think of a good example offhand) here's a case of a one-many (as well as a many-many) situation: people & phone numbers. Let's look at it as a one-many first: Johnny Blue has a cellphone, a home phone, and a work phone. To normalize your DB properly you'd want two tables: Table: people attribute: person_id (numeric, auto-incrememnt, unique) attribute: name (you might want several attributes for first, last, middle, salutation, etc...) Table: phones attribute: phone_id (numeric, auto-incrememnt, unique) attribute: person_id (this is where your DB gets 'relational'.. you're relating this record to a specific record in an other table) attribute: number (unique) attribute: type (LIST: cell, home, work, etc.) Okay, so that works as long as one person has one or more phones, but what if a phone "has" more than one person? Such as my wife, my son, and myself all having the same number? Okay, now you're talking many-many, and you need a third table to link the people and phones bi-directionally: Table: people_phones attribute: person_id (numeric, auto-incrememnt, unique) attribute: phone_id (numeric, auto-incrememnt, unique) And you can remove the person_id from the phones table because now it's in this new table. In practice now, Johnny has 3 phones, but his wife has 1, and it's the same as Johnny's home number... If you say Johhny is person_id=1 and his wife =2, and that his home# is phone_id=1, cell =2, work =3 then the data in the people_phones table looks like this: row1: person_id=1, phone_id=1; row2: person_id=1, phone_id=2; row3: person_id=1, phone_id=3; row4: person_id=2, phone_id=1; I hope that makes sense!

Hi! I was trying out Umbrello, and it seems very clunky, and it crashed on me after only a few minutes... Are there any good *nix (I'm using Ubuntu (Debian) linux) E-R modeling tools? Free, or otherwise? In a perfect world, It'd be great to have a tool that could help me build a visual ER model, and also have the ability to import a SQL structure dump, or export one as well! Thanks in advance! ^Curtis

My bad, that is exactly what you said, isn't it.... I think I've been putting too many late hours into this project! For now, though, I think I'm going to take a few days off from it. Thank you again for the invaluable help. You have been most kind and generous!

I am sooooo close to getting this correct..... Right now my report is almost perfect: [!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Mrs. Sobel, Janice A. home owner 123 New Street London, Ontario M1C 1T5 home - 647-555-1234 personal cell - 647-555-9862 Technician 14 The Donway West Don Mills, Ontario M3B 1B6 school - 416-555-7182[/quote] The only problem is -- in my address_role table, Mrs. Sobel (name_id=2) is linked to the second address shown (address_id=3) with the role_title of Teacher (role_id=3) and yet the result I'm getting has assigned her to the content of the roles table as if her linked role_id were 5.... I'm not yet sure why this is happening.... Here's the query: @mysql_query("SELECT r.* FROM roles r INNER JOIN address_role ar USING (role_id) WHERE ar.address_id='$id_address'") I tried adding "LIMIT 1" to the tail of the query and it fixed her report, but then Mr. Blythe who shares the address at 14 The Donway West reports as being a teacher when *he's* supposed to be the technician!!!! I'll go pull my hair out for a while -- I have some family stuff to take care of, then I'll be back at it trying to figure out what I'm doing wrong. Of course if it's obvious to someone, I'd be more than pleased to hear some details :) [b]NEVERMIND[/b] rookie mistake, I forgot a small part of the expression in the query, it ought to be: @mysql_query ("SELECT r.* FROM roles r INNER JOIN address_role ar USING (role_id) WHERE ar.address_id='$id_address' AND ar.name_id='$id_name'")

[!--quoteo(post=351736:date=Mar 4 2006, 08:27 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 4 2006, 08:27 PM) [snapback]351736[/snapback][/div][div class=\'quotemain\'][!--quotec--] Yeah, I would probably create a roles table and then store links to it in the name_address table. It looks like address_role should definitely come out of the address table; it just doesn't belong there. [/quote] When I first read that, I thought it would indeed be better than a role_address table with only role_id and address_id, but then after thinking more about it I though about how that would work with the requirement that two or more people might share an address but have a different role.... Your suggestion would provide more normalization, but (unless I'm missing something) still locks a single role to a single address. I think what I need to get working is a pair of new tables.... 'roles' with role_id and role_title and then a table 'address_role' that has not only address_id and role_id, but also name_id so that it properly links a specific name that's linked to a specific address with a specific role... When/if I get it working I'll post back with my results!

your if ($checkempty) is only looking at the number of rows returned... is that what you want? If you want to check a specific field try something like: [code] $query_check = @mysql_query("SELECT * FROM `islands`   WHERE fieldiwannaupdate!='' AND ID='$setfield'") or die(mysql_error));      while ($row = mysql_fetch_assoc($query_check)) {           $checker = $row['fieldiwannaupdate'];           if ($checker = '') { $sql = mysql_query("UPDATE `mytable` SET `fieldiwannaupdate` = '$thingiwannaupdateto' WHERE `ID` ='setfield' LIMIT 1; ") or die (mysql_error()); break;           }      } [/code] I think.... it's something to try anyway :)

I can't give you a straight answer, but I see that you can use regexp in MySQL... here's just one page I found, but it should be a good starting point.... [a href=\"http://www.tech-recipes.com/mysql_tips484.html\" target=\"_blank\"]one page discussing regular expressions in MySQL selects[/a] I'd highly recommend exporting the data, and re-importing it into two tables though, because it'll save you time down the road when any similar situations arise.

Side-Note: After doing some further testing, I notice that I've failed to account for one of the requirements, that being that two or more clients may share an address, but of course they may have seperate roles at said address. My thinking is that I need to remove the addresses.address_role field, and create a table called roles with the fields: role_id, role_name, address_id, name_id So that it can have a one (addresses table) to many (roles table) relationship that properly links the roles to the correct names and addresses.... I'll have to ponder this more, but it's not a rush, as it's not a true concern in this first phase of the project. For now, I'm not seeing that it'd be important enough to go to extreme data-normalizing and creating roles as a many-many with an indexing table for each of names and addresses -- if there are a handful of records in roles that duplicate a role_name field I think the reduced complexity pays for the lack of normalizing. (But what if a role changes from "janitor" to "maintenance engineer" -- everyone with that role should be updatable with a single change, so maybe many-many is the only real way to go.... Anyway, that's a battle for an other day!)

wickning1, Thank you! That's almost exactly what I need... now I can study it to figure out what the join-logic is that drives the proper functionality! I did have to make a couple minor tweaks, but I was able to use the code almost exactly as you corrected it. (for instance, I made a change so that the formatting would be clearer, and it would only print "extension: <blah>" if there was an extension in the row.) [code]         if (!$extension_phone == '') {$extend = " (extension: ".$extension_phone.")"; }         echo $desc_phone." - ".$number_phone.$extend."<br />";         unset($extend); [/code] How much do I owe you? :)

[!--quoteo(post=351559:date=Mar 4 2006, 06:22 AM:name=OriginalSunny)--][div class=\'quotetop\']QUOTE(OriginalSunny @ Mar 4 2006, 06:22 AM) [snapback]351559[/snapback][/div][div class=\'quotemain\'][!--quotec--] Hi, I just need to know the command i require to change the column name of a table. The table is called 'products' the column is called 'pic' and the new name i want to change the column to is called 'prodpic'. Thanks. [/quote] Hey, I know the answer to this one!!!! ALTER TABLE `products` RENAME `prodpic` ;

Also, it sounds like a good opportunity to learn how to get your MySQL server to use SSL! (Not that I can tell you how, but it's something on my plate to learn as well... After I get my 6-table many-many-many thingy figured out I'll be moving on to the MySQL security issues. Maybe I'll post a tutorial on my website for both issues when the time comes, because I'm not having much luck finding a resource that I can wrap my head around for the many-many-many issue, and thre can never be too many security tutorials :)

Yeah, I know it's a bit much to grok... To try to break-down the main points: 1. I'm pretty sure my DB schema is properly set-up for these many-many relations, but I'll attach it to the end of this post in-case it's helpful info. 2. My web-report isn't posting the phone numbers that have a dual-link to both the: name (client) and a given address. Perhaps this is a fault in my schema, but I think it's actually a fault in my PHP logic & SQL queries. For example, say Client A has a home address & work address, the report should show both addresses, and the home address should include the home phone number(s), while the work address should show the work phone number(s)... this does need to be in three many-many tables so that any number of addresses or phone numbers can be associated with a client, and there may be cases where many clients have the same address and/or phone numbers too. You can see my current demo at [a href=\"http://jzechner.spectraldesign.net/\" target=\"_blank\"]my DB demo[/a] 3. I suspect that I'm working too hard to get the desired result -- someone who's done a few many-many DBs is probably familiar with a more elegant way to set-up my queries for more efficiency in the reporting. So, here's the schema, in brief: [code] -- Table structure for table `addresses` DROP TABLE IF EXISTS `addresses`; CREATE TABLE IF NOT EXISTS `addresses` (   `address_id` smallint(6) unsigned NOT NULL auto_increment,   `address_street` varchar(45) NOT NULL default '',   `address_suite` varchar(15) NOT NULL default '',   `address_city` varchar(35) NOT NULL default '',   `address_province` varchar(45) NOT NULL default '',   `address_postcode` varchar(15) NOT NULL default '',   `address_role` varchar(25) NOT NULL default '',   PRIMARY KEY  (`address_id`) ) TYPE=MyISAM; -- Table structure for table `names` DROP TABLE IF EXISTS `names`; CREATE TABLE IF NOT EXISTS `names` (   `name_id` smallint(6) unsigned NOT NULL auto_increment,   `name_first` varchar(25) NOT NULL default '',   `name_last` varchar(25) NOT NULL default '',   `name_middle` varchar(25) NOT NULL default '',   `name_title` varchar(10) NOT NULL default '',   PRIMARY KEY  (`name_id`) ) TYPE=MyISAM; -- Table structure for table `phones` DROP TABLE IF EXISTS `phones`; CREATE TABLE IF NOT EXISTS `phones` (   `phone_id` smallint(6) unsigned NOT NULL auto_increment,   `phone_number` varchar(25) NOT NULL default '',   `phone_extension` varchar(10) NOT NULL default '',   `phone_description` varchar(25) NOT NULL default '',   PRIMARY KEY  (`phone_id`) ) TYPE=MyISAM; -- Table structure for table `name_address` DROP TABLE IF EXISTS `name_address`; CREATE TABLE IF NOT EXISTS `name_address` (   `na_id` mediumint(9) NOT NULL auto_increment,   `name_id` smallint(6) NOT NULL default '0',   `address_id` smallint(6) NOT NULL default '0',   PRIMARY KEY  (`na_id`,`name_id`,`address_id`) ) TYPE=MyISAM; -- Table structure for table `name_phone` DROP TABLE IF EXISTS `name_phone`; CREATE TABLE IF NOT EXISTS `name_phone` (   `np_id` mediumint(9) NOT NULL auto_increment,   `name_id` smallint(6) NOT NULL default '0',   `phone_id` smallint(6) NOT NULL default '0',   PRIMARY KEY  (`np_id`,`name_id`,`phone_id`) ) TYPE=MyISAM; -- Table structure for table `address_phone` DROP TABLE IF EXISTS `address_phone`; CREATE TABLE IF NOT EXISTS `address_phone` (   `ap_id` mediumint(9) NOT NULL auto_increment,   `address_id` smallint(6) NOT NULL default '0',   `phone_id` smallint(6) NOT NULL default '0',   PRIMARY KEY  (`ap_id`,`address_id`,`phone_id`) ) TYPE=MyISAM; [/code] and the fictitious data-set: [code] -- Dumping data for table `addresses` INSERT INTO `addresses` VALUES (1, '123 New Street', '', 'London', 'Ontario', 'M1C 1T5', 'Home Owner'); INSERT INTO `addresses` VALUES (2, '423 Bright Avenue', 'Suite 4330', 'Toronto', 'Ontario', 'M4X 3F8', 'President - Ramco'); INSERT INTO `addresses` VALUES (3, '14 The Donway West', '', 'Don Mills', 'Ontario', 'M3B 1B6', 'Teacher - Don Mills Collegiate'); -- Dumping data for table `names` INSERT INTO `names` VALUES (1, 'James', 'Sobel', 'W.', 'Mr.'); INSERT INTO `names` VALUES (2, 'Janice', 'Sobel', 'A.', 'Mrs.'); -- Dumping data for table `phones` INSERT INTO `phones` VALUES (1, '647-555-1234', '', 'home'); INSERT INTO `phones` VALUES (2, '647-555-4321', '', 'office cell'); INSERT INTO `phones` VALUES (3, '647-555-9862', '', 'personal cell'); INSERT INTO `phones` VALUES (4, '416-555-6789', '', 'office'); INSERT INTO `phones` VALUES (5, '416-555-6780', '', 'office fax'); INSERT INTO `phones` VALUES (6, '416-555-7182', '', 'school'); -- Dumping data for table `name_address` id - name - address INSERT INTO `name_address` VALUES (1, 1, 1); INSERT INTO `name_address` VALUES (2, 1, 2); INSERT INTO `name_address` VALUES (3, 2, 1); INSERT INTO `name_address` VALUES (4, 2, 3); -- Dumping data for table `address_phone` id - address - phone INSERT INTO `address_phone` VALUES (1, 1, 1); INSERT INTO `address_phone` VALUES (2, 1, 3); INSERT INTO `address_phone` VALUES (3, 2, 2); INSERT INTO `address_phone` VALUES (4, 2, 4); INSERT INTO `address_phone` VALUES (5, 2, 5); INSERT INTO `address_phone` VALUES (6, 3, 6); -- Dumping data for table `name_phone` id - name - phone INSERT INTO `name_phone` VALUES (1, 1, 1); INSERT INTO `name_phone` VALUES (2, 1, 2); INSERT INTO `name_phone` VALUES (3, 1, 4); INSERT INTO `name_phone` VALUES (4, 1, 5); INSERT INTO `name_phone` VALUES (5, 2, 1); INSERT INTO `name_phone` VALUES (6, 2, 3); INSERT INTO `name_phone` VALUES (7, 2, 6); [/code]

Hello again, It's been so long since I've been on the PHPFreak boards I had to create a new login. Anyway, I'm trying to do my first DB with 3 many-many tables... names: name_id, first, last, middle, title addresses: address_id, street, suite, city, province, postcode, role phones: phone_id, number, extension, description to handle the many-many relationships I have 3 additional tables: name_address: id, name_id, address_id name_phone: id, name_id, phone_id address_phone: id, address_id, phone_id Any name could have one or more addresses, plus one or more phone numbers Any address could have one or more names (residents), plus one or more phone numbers Any phone could have one or more names (users), plus it would be linked to a specific address Complicated enough yet? Well, I'm admitedly an armchair coder, and I'm sure there's a more elegant way to get the DB (via PHP/HTML) to report: for any name (person) what addresses are in the DB, and for each of those addresses, what are the phone numbers in the database? At the point I've reached I've got it recalling the correct address(es) for any given name (person), but for some reason I'm not getting it to report the phone numbers linked to that name at each address.... [b] (non-working code removed)[/b]