Re: table design question
Hi Peter, Thanks for the reply. So are you saying like this? Repair RID INT, Date DATE, Tech_ID INT, R_TYPE_ID INT 1 2015-07-28 3243 3 2 2015-06-15 1253 1 Repair_details ID, APL_TYPE VARCHAR(35), REPAIR_CODE CHAR(4), DESC 1 Refridgerator C compressor 2 Wash Mach MC Motor Coupler 3 Dish Washer SA Spray Arm 4 Refridgerator DP Drain Pan Not sure what you mean by repeating details.What would the look up table look like? Thanks 2015-07-29 9:38 GMT-05:00 peter.braw...@earthlink.net: one table with a long ENUM column that contains repairs that could be attributed to any appliance or different repair tables for each appliance. The first would stick you with extending the enum column forever, the second would stick you with unmanageable table glut, so you need the traditional relational solution---a parent repairs table to track common repair attributes, a child table to track the details, and lookup tables to track repeating details. PB Original Message From: Richard Reina gatorre...@gmail.com Reply-To: Richard Reina gatorre...@gmail.com Date: 07/29/15 10:19 AM To: mysql@lists.mysql.com mysql@lists.mysql.com Cc: Sub: table design question If I were to create a database table(s) to tract most common repairs to different appliances I can't decide if it would be better to create one table with a long ENUM column that contains repairs that could be attributed to any appliance or different repair tables for each appliance. All the tables would describe the same thing -- a repair -- however the the things being repaired are different in nature which means a great deal of types of repairs that do not relate. Here is an example. repair_wash_mach ID INT, Date DATE, Tech_ID INT, Type ENUM( 'leak', 'motor_coupler', 'pump', 'controls', 'agitator') repair_dish_washer ID INT, Date DATE, Tech_ID INT, Type ENUM( 'drain_arm', 'drive_belt', 'door_latch', 'spray_arm', 'drain_valve') repair_refridgerator ID INT, Date DATE, Tech_ID INT, Type ENUM( 'circ_fan', 'compressor', 'disps_line', 'drain_pan', 'feeler_arm') Or since they are all repairs should they be in one table with a REALLY long ENUM table -- that will need to me altered as the number of appliances will most likely increase? ID INT, APPLIANCE VARCHAR(35), Date DATE, Tech_ID INT, Type ENUM( 'leak', 'motor_coupler', 'pump', 'controls', 'agitator', 'drain_arm', 'drive_belt', 'door_latch', 'spray_arm', 'drain_valve', 'circ_fan', 'compressor', 'disps_line', 'drain_pan', 'feeler_arm') End Original Message
Re: table design question
Hi Richard, On 7/29/2015 10:19 AM, Richard Reina wrote: If I were to create a database table(s) to tract most common repairs to different appliances I can't decide if it would be better to create one table with a long ENUM column that contains repairs that could be attributed to any appliance or different repair tables for each appliance. All the tables would describe the same thing -- a repair -- however the the things being repaired are different in nature which means a great deal of types of repairs that do not relate. Here is an example. repair_wash_mach ID INT, Date DATE, Tech_ID INT, Type ENUM( 'leak', 'motor_coupler', 'pump', 'controls', 'agitator') repair_dish_washer ID INT, Date DATE, Tech_ID INT, Type ENUM( 'drain_arm', 'drive_belt', 'door_latch', 'spray_arm', 'drain_valve') repair_refridgerator ID INT, Date DATE, Tech_ID INT, Type ENUM( 'circ_fan', 'compressor', 'disps_line', 'drain_pan', 'feeler_arm') Or since they are all repairs should they be in one table with a REALLY long ENUM table -- that will need to me altered as the number of appliances will most likely increase? ID INT, APPLIANCE VARCHAR(35), Date DATE, Tech_ID INT, Type ENUM( 'leak', 'motor_coupler', 'pump', 'controls', 'agitator', 'drain_arm', 'drive_belt', 'door_latch', 'spray_arm', 'drain_valve', 'circ_fan', 'compressor', 'disps_line', 'drain_pan', 'feeler_arm') I would suggest a table of appliances, a table of components, and a table of repairs something like this... repair_tasks( task_id int auto_increment , task_description varchar(25) , appliance_id int not null , component_id int not null ) That way you can have two tasks for the same device. For example, A task of attach door seal would associate the fields (refrigerator, door seal). So would replace door seal. So would order door seal from warehouse. I would not use ENUMS, you would run out of options too quickly. My examples are extremely simplified but hopefully you can see the storage pattern I am suggesting. Regards, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
table design question
If I were to create a database table(s) to tract most common repairs to different appliances I can't decide if it would be better to create one table with a long ENUM column that contains repairs that could be attributed to any appliance or different repair tables for each appliance. All the tables would describe the same thing -- a repair -- however the the things being repaired are different in nature which means a great deal of types of repairs that do not relate. Here is an example. repair_wash_mach ID INT, Date DATE, Tech_ID INT, Type ENUM( 'leak', 'motor_coupler', 'pump', 'controls', 'agitator') repair_dish_washer ID INT, Date DATE, Tech_ID INT, Type ENUM( 'drain_arm', 'drive_belt', 'door_latch', 'spray_arm', 'drain_valve') repair_refridgerator ID INT, Date DATE, Tech_ID INT, Type ENUM( 'circ_fan', 'compressor', 'disps_line', 'drain_pan', 'feeler_arm') Or since they are all repairs should they be in one table with a REALLY long ENUM table -- that will need to me altered as the number of appliances will most likely increase? ID INT, APPLIANCE VARCHAR(35), Date DATE, Tech_ID INT, Type ENUM( 'leak', 'motor_coupler', 'pump', 'controls', 'agitator', 'drain_arm', 'drive_belt', 'door_latch', 'spray_arm', 'drain_valve', 'circ_fan', 'compressor', 'disps_line', 'drain_pan', 'feeler_arm')
Re: table design question
From: Richard Reina gatorre...@gmail.com I want to create a US geography database. So far I have categories such as state nick names (some states have more than one), state mottos (text 25 to 150 characters), state name origins (100-300 characters), state trivial facts, entry into union. My question is; would it be better to keep at least some of this information in separate tables... To me, the key question is cardinality. You gave a big clue with some states have more than one. This cardinality rule clearly indicates you need a separate table for nick names. I'd look carefully at cardinality, and any field in which you can say, some states may have more than one, put it in a separate table. (One exception to cardinality-driven table design would be if a field is a clearly defined, relatively unchanging set of constants. The classic example is when different states in a process need to be recorded -- membership might include the set applied, paid, accepted, withdrawn. You could have multiple states in a SET field, which would be much less cumbersome than having a fifth-normal-form join table.) A low-energy policy allows for a wide choice of lifestyles and cultures. If, on the other hand, a society opts for high energy consumption, its social relations must be dictated by technocracy and will be equally degrading whether labeled capitalist or socialist. -- Ivan Illich Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
table design question
I want to create a US geography database. So far I have categories such as state nick names (some states have more than one), state mottos (text 25 to 150 characters), state name origins (100-300 characters), state trivial facts, entry into union. My question is; would it be better to keep at least some of this information in separate tables like: state_basic ID | name | Incorporation | Entry in Union| Name_origin | Motto state_nicknames ID | name | nick_name| state_trivia ID | name | fact or would it be batter for queries to try to put all this information in one table? Thanks, Richard
RE: table design question
-Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Monday, September 19, 2011 9:55 AM To: mysql@lists.mysql.com Subject: table design question I want to create a US geography database. So far I have categories such as state nick names (some states have more than one), state mottos (text 25 to 150 characters), state name origins (100-300 characters), state trivial facts, entry into union. My question is; would it be better to keep at least some of this information in separate tables like: state_basic ID | name | Incorporation | Entry in Union| Name_origin | Motto state_nicknames ID | name | nick_name| state_trivia ID | name | fact or would it be batter for queries to try to put all this information in one table? [JS] Use separate tables. Unless you have a //very// good reason, you should always try to normalize your data. In other words, use separate tables unless you are positive that you will //always// have 1:1 relationships between the various fields. For example, even such a simple thing as the data of incorporation might have more than one value in the case of the original colonies, the independent republics (Texas, California), and (I'm not sure about these) the Dakotas and West Virginia. Did you know that Maine was once part of Massachusetts? You could put that kind of thing into a trivia record, but that might make it harder to use in the future. My personal philosophy is that it is easier to scramble an egg than to unscramble it. You might someday need to keep track of which states were originally part of other states. And remember, those things that will never happen will happen the day before your vacation. The last thing you want to hear is Richard, before you leave I need you to... (I have 45 years of experience with that.) Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.giiresearch.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: table design question
I would design three tables: Table1 (states): ID, name, abbreviation Table2 (state_item): ID, state_id (from states), item_id (from item_type), item_value (varchar) Table3 (item_type): ID, item_name Into the item_type table you can insert: Nick Name Motto Name origin Facts SomeOtherDataPoint SomeOtherDataPoint2 etc Now, you can have as many nick names per state as needed, some states may have 1, some 50, etc. Same for every other data point you want to keep track of for each state as well. On Mon, Sep 19, 2011 at 8:55 AM, Richard Reina gatorre...@gmail.com wrote: I want to create a US geography database. So far I have categories such as state nick names (some states have more than one), state mottos (text 25 to 150 characters), state name origins (100-300 characters), state trivial facts, entry into union. My question is; would it be better to keep at least some of this information in separate tables like: state_basic ID | name | Incorporation | Entry in Union| Name_origin | Motto state_nicknames ID | name | nick_name| state_trivia ID | name | fact or would it be batter for queries to try to put all this information in one table? Thanks, Richard -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: table design question
Thank you very much for all the insightful advice. I will keep the separated. 2011/9/19 Jerry Schwartz je...@gii.co.jp -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Monday, September 19, 2011 9:55 AM To: mysql@lists.mysql.com Subject: table design question I want to create a US geography database. So far I have categories such as state nick names (some states have more than one), state mottos (text 25 to 150 characters), state name origins (100-300 characters), state trivial facts, entry into union. My question is; would it be better to keep at least some of this information in separate tables like: state_basic ID | name | Incorporation | Entry in Union| Name_origin | Motto state_nicknames ID | name | nick_name| state_trivia ID | name | fact or would it be batter for queries to try to put all this information in one table? [JS] Use separate tables. Unless you have a //very// good reason, you should always try to normalize your data. In other words, use separate tables unless you are positive that you will //always// have 1:1 relationships between the various fields. For example, even such a simple thing as the data of incorporation might have more than one value in the case of the original colonies, the independent republics (Texas, California), and (I'm not sure about these) the Dakotas and West Virginia. Did you know that Maine was once part of Massachusetts? You could put that kind of thing into a trivia record, but that might make it harder to use in the future. My personal philosophy is that it is easier to scramble an egg than to unscramble it. You might someday need to keep track of which states were originally part of other states. And remember, those things that will never happen will happen the day before your vacation. The last thing you want to hear is Richard, before you leave I need you to... (I have 45 years of experience with that.) Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.giiresearch.com
design question
i'm just looking for rough ideas here... i've got a table that has 31 fields. most of them need to be there (entry time, exit time, entry lat, etc). however, i've got 4 fields that i query this db with that should generally be unique... well, really 3 fields that should be unique, because the 'name' field is what it sounds like - a spoken word - and two things can have the same name. so, i've got three other fields, two 'unsigned big int's and one varchar(10) that's a callsign. all of these three fields should agree with each other across entries this isn't always the case. there are data abnormalities. so, i'm curious of the best way to store and look this data up. my first thought was just to have 4 separate tables with two fields - the value and the unique id. however, sense i generally query these things together, i thought that maybe they should all go into one separate table with a uid that would differ for bad data types - those could easily be found and dealt with. my other thought was to have the separate tables and index the uid's in the main table together? i've got scripts that import this data. so, though i've got this data in a db, i'm just going to rewrite my import script to use the new schema. so, think of this as a new db with no data issues. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Design question.
I maintain a little open source project that deals with IDS alert data. I want to add IP reputation to my event queries and I am stuck on how I should implement it. The user will have the option of bringing in lists from different providers and the limit will not be fixed. These lists will be a single column of IP addresses. list 1: IP listing list 2: IP listing list 3: IP listing ... There can, and most likely will be duplication of addresses across the different lists. The number of lists that a host is a member of will be an indication of its reputation. The desired result will be something like: event count | event signature | src ip | country | ip reputation | dst ip | country | ip reputation The lists will be updated once each day or on demand. I already have a mappings table that provides country information for ip's in the event table which is joined during the event queries. The mappings table contains a little under 500,000 addresses and grows slowly - say 50 to 100 addresses / day. As new ip's appear in the event table, they are mapped to a country. Questions: 1) Should I just create a new table for every list the user adds and then do joins on these? 2) Should I put the lists in 1 table somehow? 3) As the lists are done daily, should I just run a midnight task that parses each list and adds the information to the mappings table. I have no idea what the format would look like. I was thinking of creating a varchar and have something like: list1|list2|list9|list20 and then just breaking it out in the code. The entire table would of course need to be scanned each day to check whether or not an address had been taken off a list. (efficiency?) Any comments/suggestions would be greatly appreciated. Thanks. -- Paul Halliday http://www.pintumbler.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Table design question
Hi, I'm creating a application which hosts football matches and I want to record the player appearances, goals etc. I was thinking of having a record for each player as follows : appearance_id season_id player_id team_id competition_id appearance goals yellow_card red_card date_played Is this the sort of standard layout, recommended for such application. From here, I can then count number of appearances, goals scored etc within a season or for a team_id ? Cheers Neil
design question
hi there, I am a casual database tinkerer that has to build a new database. so please do not fall down laughing if I ask stupid questions .. the problem at hand is that I want to create tables with the following structure: - suppliers they produce/deal-with 0 to n products - products a product is produced/sold by 1-n suppliers it can have 0-1 declaration - declarations these are product specsheets my questions are now: what indices and actions do I have to create to link these tables ? in the following script created by by MySQL-workbench. there is the table tblProducts_has_tblSupplier which is linked to both tblSupplier and tblProducts by foreign keys. How do I guaranty integrity when deleting a product or supplier?? do i have to do that using triggers? thanks for your insigth robert SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; CREATE SCHEMA IF NOT EXISTS `energie` ; USE `energie`; -- - -- Table `energie`.`tblProducts` -- - DROP TABLE IF EXISTS `energie`.`tblProducts` ; CREATE TABLE IF NOT EXISTS `energie`.`tblProducts` ( `idtblProducts` INT(11) NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NOT NULL , `simplesystem` TINYINT(4) NULL DEFAULT 0 , `kompaktenergiezentrale` TINYINT(4) NULL DEFAULT 0 , `device_with_humidity_recovery` TINYINT(4) NULL DEFAULT 0 , `room_ventilator` TINYINT(4) NULL DEFAULT 0 , `comfort_regulation` TINYINT(4) NULL DEFAULT 0 , `heat_pump` TINYINT(4) NULL DEFAULT 0 , `outlet` TINYINT(4) NULL DEFAULT 0 , `air_dispersing_system` TINYINT(4) NULL DEFAULT 0 , `heat_exchanger` TINYINT(4) NULL DEFAULT 0 , `heat_recovery` TINYINT(4) NULL DEFAULT 0 , `humidity_recovery` TINYINT(4) NULL DEFAULT 0 , `sound_suppressor` TINYINT(4) NULL DEFAULT 0 , `pollen_filter` TINYINT(4) NULL DEFAULT 0 , `groundsregister` TINYINT(4) NULL DEFAULT 0 , `single_room` TINYINT(4) NULL DEFAULT 0 , `one_family_house` TINYINT(4) NULL DEFAULT 0 , `multi_family_house` TINYINT(4) NULL DEFAULT 0 , PRIMARY KEY (`idtblProducts`) ) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; -- - -- Table `energie`.`tblDeclaration_vent` -- - DROP TABLE IF EXISTS `energie`.`tblDeclaration_vent` ; CREATE TABLE IF NOT EXISTS `energie`.`tblDeclaration_vent` ( `idtblDeclaration_vent` INT NOT NULL AUTO_INCREMENT , `manufacturer` VARCHAR(45) NOT NULL , `name` VARCHAR(45) NOT NULL , `flowrate` INT NULL DEFAULT 0 , `humidity_recovery` INT NULL DEFAULT 1 , `energy_class` VARCHAR(1) NOT NULL , `energy_class_value` VARCHAR(10) NOT NULL , `hygiene_class` VARCHAR(1) NOT NULL , `noise_class` VARCHAR(1) NOT NULL , `audited_by` VARCHAR(45) NULL DEFAULT '' , `audited_date` DATE NULL DEFAULT NULL , `autition_report` VARCHAR(45) NULL DEFAULT '0-0-0' , `picture_name` VARCHAR(45) NULL DEFAULT '' , `remarks` TEXT NULL DEFAULT NULL , `reindex` BOOLEAN NULL DEFAULT 1 , `tblProducts_idtblProducts` INT(11) NULL , PRIMARY KEY (`idtblDeclaration_vent`) , CONSTRAINT `fk_tblDeclaration_vent_tblProducts` FOREIGN KEY (`tblProducts_idtblProducts` ) REFERENCES `energie`.`tblProducts` (`idtblProducts` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB COMMENT = 'table with declaration data for ventilation' PACK_KEYS = 1; CREATE INDEX imanufacturer ON `energie`.`tblDeclaration_vent` (`manufacturer` ASC) ; CREATE INDEX ienergy_class ON `energie`.`tblDeclaration_vent` (`energy_class` ASC) ; CREATE INDEX ihygiene_class ON `energie`.`tblDeclaration_vent` (`hygiene_class` ASC) ; CREATE INDEX inoise_class ON `energie`.`tblDeclaration_vent` (`noise_class` ASC) ; CREATE UNIQUE INDEX imanufaturer_name ON `energie`.`tblDeclaration_vent` (`manufacturer` ASC, `name` ASC) ; CREATE INDEX iname ON `energie`.`tblDeclaration_vent` (`name` ASC) ; CREATE INDEX fk_tblDeclaration_vent_tblProducts ON `energie`.`tblDeclaration_vent` (`tblProducts_idtblProducts` ASC) ; -- - -- Table `energie`.`tblSupplier` -- - DROP TABLE IF EXISTS `energie`.`tblSupplier` ; CREATE TABLE IF NOT EXISTS `energie`.`tblSupplier` ( `idtblSupplier` INT(11) NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NOT NULL , `extraname` VARCHAR(45) NOT NULL , `description` VARCHAR(45) NOT NULL , `address` VARCHAR(45) NOT NULL , `extraaddress` VARCHAR(45) NOT NULL , `zip` INT(20) NOT NULL , `city` VARCHAR(45) NOT NULL , `pob` INT(20) NOT NULL , `country` VARCHAR(45) NOT NULL , `language` VARCHAR(45) NOT NULL , `email` VARCHAR(45) NOT NULL , `url` VARCHAR(60) NOT NULL , `phone` VARCHAR(45) NOT NULL , `fax` VARCHAR(45) NOT NULL , `responsibleperson` VARCHAR(45) NOT NULL ,
Hypothetical design question regarding keyword searching
I have been looking at stock photo sites lately, started wondering how they are doing their keyword searched. Given a potential for millions of images, each with x keywords, I have come up with two approaches... Approach one Images table, with a parent id Keywords table, each keyword would be its own row, and also link back to the parent id. You could then search for the keyword, get the parent id's, and do a IN (...) search against the images, thereby pulling up the images that have those keywords. Potentially problematic as assuming million of images, and average 10 keywords per image, you end up with a keyword table that has 10's of millions of rows, along with lots of duplicate keywords. Approach two Use a link table, this resolves the duplicate keyword issue, and I am sure there will be many dupes. However, it adds a third table. This would complicate the JOIN query. Are there other approaches? Which approach would yield the best performance for growth issue? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hypothetical design question regarding keyword searching
Ysgrifennodd Scott Haneda: I have been looking at stock photo sites lately, started wondering how they are doing their keyword searched. Given a potential for millions of images, each with x keywords, I have come up with two approaches... snip / Is this the sort of thing you're looking for: http://en.wikipedia.org/wiki/Inverted_index It's the sort of structure used in library software. Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hypothetical design question regarding keyword searching
Greetings, Buy 'Building Scalable Web Sites' [http://www.oreillynet.com/pub/pr/1582], authored by the chief architect of Flickr. Among other interesting topics, he describes the system they use for full-text search. Its pretty simple, though this is just one of the ways you can solve this problem. Good luck, Mark On 7/19/07, Peter Bradley [EMAIL PROTECTED] wrote: Ysgrifennodd Scott Haneda: I have been looking at stock photo sites lately, started wondering how they are doing their keyword searched. Given a potential for millions of images, each with x keywords, I have come up with two approaches... snip / Is this the sort of thing you're looking for: http://en.wikipedia.org/wiki/Inverted_index It's the sort of structure used in library software. Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Mark Papadakis http://www.markpapadakis.com/ [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Layout (Design) Question
This isn't exactly what I was looking for, but it works like a charm for both my needs and the sys admins. Go figure everyone is happy now! Many Thanks! -Tyler Kishore Jalleda wrote: The delete would definitely depend upon the size of the record set being deleted, anyway assuming I comprehended your situation correctly ,I would suggest using the Merge storage engine for your needs , and keep every single day of data in a seperate MyISAM table, and merge all those tables together into a single merge table, so when you want to do any maintenance on any data older than x days just alter the table and take that mailxx table out of the merge table , delete it compress it, do anyhting with it and then just add a other one (you could easily run a nightly cron job for this) Please look at http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html Kishore Jalleda On 1/31/07, [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: I'm trying to wrap my head around a performance problem our institution is having on our Mysql Server which hosts all of our logs from around campus. Specifically our MailLogs tables. What I'm having a hard time with is we have metadata such as: CREATE TABLE mail00 ( host varchar(32) default NULL, fromMTA varchar(44) default NULL, nextMTA varchar(44) default NULL, messageID varchar(44) default NULL, messageID2 varchar(44) default NULL, sender varchar(80) default NULL, recipient varchar(120) default NULL, recipient2 varchar(120) default NULL, date date default NULL, time time default NULL, program varchar(44) default NULL, ACTION varchar(44) default NULL, detail varchar(120) default NULL, msg text, seq int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (seq), KEY host (host), KEY sender (sender), KEY recipient (recipient), KEY MessageID (messageID), KEY seq (seq), KEY time (time), KEY date (date), KEY ACTION (ACTION), KEY messageID2 (messageID2), KEY fromMTA (fromMTA) ) TYPE=MyISAM MAX_ROWS=9900; We might end up storing two to three gigs of logging data per day from our mail servers. When we had the process setup to purge data out of this table that is older than 14 days, it would lock and take great deal of time process the request. My question is, is the structure of my table the problem or is it just the size of the data that is just going to take that long due to Disk IO? This isn't a table structure that I came up with, I'm just looking to optimize the performance of the server. Currently around midnight the sysadmin currently drops table 13, then moves 12 - 13, 11 - 12, etc... and creates a 00. All of this is because it takes to long to purge out one days worth of data . This will eventually cause a problem when we try to develop a program to scan the logs, it will need to scan through all 14 tables instead of just one. Is there a better way that mitigates the performance and flexibility? Or just a better way in general? Thanks, -Tyler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database Layout (Design) Question
I'm trying to wrap my head around a performance problem our institution is having on our Mysql Server which hosts all of our logs from around campus. Specifically our MailLogs tables. What I'm having a hard time with is we have metadata such as: CREATE TABLE mail00 ( host varchar(32) default NULL, fromMTA varchar(44) default NULL, nextMTA varchar(44) default NULL, messageID varchar(44) default NULL, messageID2 varchar(44) default NULL, sender varchar(80) default NULL, recipient varchar(120) default NULL, recipient2 varchar(120) default NULL, date date default NULL, time time default NULL, program varchar(44) default NULL, ACTION varchar(44) default NULL, detail varchar(120) default NULL, msg text, seq int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (seq), KEY host (host), KEY sender (sender), KEY recipient (recipient), KEY MessageID (messageID), KEY seq (seq), KEY time (time), KEY date (date), KEY ACTION (ACTION), KEY messageID2 (messageID2), KEY fromMTA (fromMTA) ) TYPE=MyISAM MAX_ROWS=9900; We might end up storing two to three gigs of logging data per day from our mail servers. When we had the process setup to purge data out of this table that is older than 14 days, it would lock and take great deal of time process the request. My question is, is the structure of my table the problem or is it just the size of the data that is just going to take that long due to Disk IO? This isn't a table structure that I came up with, I'm just looking to optimize the performance of the server. Currently around midnight the sysadmin currently drops table 13, then moves 12 - 13, 11 - 12, etc... and creates a 00. All of this is because it takes to long to purge out one days worth of data . This will eventually cause a problem when we try to develop a program to scan the logs, it will need to scan through all 14 tables instead of just one. Is there a better way that mitigates the performance and flexibility? Or just a better way in general? Thanks, -Tyler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Layout (Design) Question
The delete would definitely depend upon the size of the record set being deleted, anyway assuming I comprehended your situation correctly ,I would suggest using the Merge storage engine for your needs , and keep every single day of data in a seperate MyISAM table, and merge all those tables together into a single merge table, so when you want to do any maintenance on any data older than x days just alter the table and take that mailxx table out of the merge table , delete it compress it, do anyhting with it and then just add a other one (you could easily run a nightly cron job for this) Please look at http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html Kishore Jalleda On 1/31/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I'm trying to wrap my head around a performance problem our institution is having on our Mysql Server which hosts all of our logs from around campus. Specifically our MailLogs tables. What I'm having a hard time with is we have metadata such as: CREATE TABLE mail00 ( host varchar(32) default NULL, fromMTA varchar(44) default NULL, nextMTA varchar(44) default NULL, messageID varchar(44) default NULL, messageID2 varchar(44) default NULL, sender varchar(80) default NULL, recipient varchar(120) default NULL, recipient2 varchar(120) default NULL, date date default NULL, time time default NULL, program varchar(44) default NULL, ACTION varchar(44) default NULL, detail varchar(120) default NULL, msg text, seq int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (seq), KEY host (host), KEY sender (sender), KEY recipient (recipient), KEY MessageID (messageID), KEY seq (seq), KEY time (time), KEY date (date), KEY ACTION (ACTION), KEY messageID2 (messageID2), KEY fromMTA (fromMTA) ) TYPE=MyISAM MAX_ROWS=9900; We might end up storing two to three gigs of logging data per day from our mail servers. When we had the process setup to purge data out of this table that is older than 14 days, it would lock and take great deal of time process the request. My question is, is the structure of my table the problem or is it just the size of the data that is just going to take that long due to Disk IO? This isn't a table structure that I came up with, I'm just looking to optimize the performance of the server. Currently around midnight the sysadmin currently drops table 13, then moves 12 - 13, 11 - 12, etc... and creates a 00. All of this is because it takes to long to purge out one days worth of data . This will eventually cause a problem when we try to develop a program to scan the logs, it will need to scan through all 14 tables instead of just one. Is there a better way that mitigates the performance and flexibility? Or just a better way in general? Thanks, -Tyler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database Layout (Design) Question
Hello, I think your major contributing problem is the database engine that you are using, MYISAM. For this type of processing, I would highly recommend you use INNODB to allow maximum concurrency and minimize your outage. With all things considered, your processing by all estimates, whether you are on DB2 Mainframe or Oracle or IMS. Deleting 2 gig of data from a table via an SQL is considered massive. Having said that, there are other methods of completing this task of purging/archiving data from a table with fewer outages. Here is what you do: 1. Unload the table into two flat files. The records that are less 14 days go into one flat file while records older than 14 days go into another. 2. At the completion of the unload process. LOAD REPLACE data using flat file that contains data less than 14days. Please email me if you need help with SQL statements, UNLOAD, or LOAD data into the table. -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 31, 2007 8:51 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: Database Layout (Design) Question The delete would definitely depend upon the size of the record set being deleted, anyway assuming I comprehended your situation correctly ,I would suggest using the Merge storage engine for your needs , and keep every single day of data in a seperate MyISAM table, and merge all those tables together into a single merge table, so when you want to do any maintenance on any data older than x days just alter the table and take that mailxx table out of the merge table , delete it compress it, do anyhting with it and then just add a other one (you could easily run a nightly cron job for this) Please look at http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html Kishore Jalleda On 1/31/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I'm trying to wrap my head around a performance problem our institution is having on our Mysql Server which hosts all of our logs from around campus. Specifically our MailLogs tables. What I'm having a hard time with is we have metadata such as: CREATE TABLE mail00 ( host varchar(32) default NULL, fromMTA varchar(44) default NULL, nextMTA varchar(44) default NULL, messageID varchar(44) default NULL, messageID2 varchar(44) default NULL, sender varchar(80) default NULL, recipient varchar(120) default NULL, recipient2 varchar(120) default NULL, date date default NULL, time time default NULL, program varchar(44) default NULL, ACTION varchar(44) default NULL, detail varchar(120) default NULL, msg text, seq int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (seq), KEY host (host), KEY sender (sender), KEY recipient (recipient), KEY MessageID (messageID), KEY seq (seq), KEY time (time), KEY date (date), KEY ACTION (ACTION), KEY messageID2 (messageID2), KEY fromMTA (fromMTA) ) TYPE=MyISAM MAX_ROWS=9900; We might end up storing two to three gigs of logging data per day from our mail servers. When we had the process setup to purge data out of this table that is older than 14 days, it would lock and take great deal of time process the request. My question is, is the structure of my table the problem or is it just the size of the data that is just going to take that long due to Disk IO? This isn't a table structure that I came up with, I'm just looking to optimize the performance of the server. Currently around midnight the sysadmin currently drops table 13, then moves 12 - 13, 11 - 12, etc... and creates a 00. All of this is because it takes to long to purge out one days worth of data . This will eventually cause a problem when we try to develop a program to scan the logs, it will need to scan through all 14 tables instead of just one. Is there a better way that mitigates the performance and flexibility? Or just a better way in general? Thanks, -Tyler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Layout (Design) Question
In the last episode (Jan 31), [EMAIL PROTECTED] said: I'm trying to wrap my head around a performance problem our institution is having on our Mysql Server which hosts all of our logs from around campus. Specifically our MailLogs tables. What I'm having a hard time with is we have metadata such as: CREATE TABLE mail00 ( (mail archive table) ) TYPE=MyISAM MAX_ROWS=9900; We might end up storing two to three gigs of logging data per day from our mail servers. When we had the process setup to purge data out of this table that is older than 14 days, it would lock and take great deal of time process the request. I say move to MySQL 5.1, and set up a range partitioned table, with one partition per day. Then before midnight you run a script that adds a new partition covering the next day and drop the oldest one. http://dev.mysql.com/doc/refman/5.1/en/partitioning.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database design question
I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. Thanks. -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design question
I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. One table, with a user_id field. If you're worried about searching through millions of records, perhaps you could have archival tables that don't normally get searched and move messages from one to other after they get old... -p -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database design question
One table, USERS Another table MESSAGES With a foreign key referencing users. Maybe a second foreign key referencing the destinating user as well. -Original Message- From: James Tu [mailto:[EMAIL PROTECTED] Sent: Monday, August 07, 2006 1:56 PM To: mysql@lists.mysql.com Subject: Database design question I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. Thanks. -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design question
Thanks everyone. Now I feel confident that one table will be fine (Tripp's stat of 30 million records put me at ease :) ). Cheers, -James On Aug 7, 2006, at 4:08 PM, John Meyer wrote: One table, USERS Another table MESSAGES With a foreign key referencing users. Maybe a second foreign key referencing the destinating user as well. -Original Message- From: James Tu [mailto:[EMAIL PROTECTED] Sent: Monday, August 07, 2006 1:56 PM To: mysql@lists.mysql.com Subject: Database design question I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. Thanks. -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design question
Wow, I didn't know that can happen. I'll definitely take that into consideration. Thanks Brent. On Aug 7, 2006, at 4:26 PM, Brent Baisley wrote: If you're looking to be put at ease, I've got a table with 250+ million records, but I've heard of people with larger tables than that on this list. You might want to also looking into using a compound primary key, meaning userid+messageid. Something like this: CREATE TABLE `message` ( `userid` int unsigned NOT NULL default '', `messageid` int unsigned NOT NULL auto_increment, `message` text, ... PRIMARY KEY (`userid`,`messageid`) ) What that does is give each user their own incrementing message id. Then you can do things like allow users to enter a message id directly with a number that would be easy for them to remember. Just an idea. - Original Message - From: James Tu [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, August 07, 2006 4:11 PM Subject: Re: Database design question Thanks everyone. Now I feel confident that one table will be fine (Tripp's stat of 30 million records put me at ease :) ). Cheers, -James On Aug 7, 2006, at 4:08 PM, John Meyer wrote: One table, USERS Another table MESSAGES With a foreign key referencing users. Maybe a second foreign key referencing the destinating user as well. -Original Message- From: James Tu [mailto:[EMAIL PROTECTED] Sent: Monday, August 07, 2006 1:56 PM To: mysql@lists.mysql.com Subject: Database design question I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. Thanks. -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design question
On 8/7/06, James Tu [EMAIL PROTECTED] wrote: If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? Hi James, There are really two elements to this problem. The first element is how quickly MySQL can extract the messages you want from a large table. This requires that you know in advance the type of queries you're going to do (all messages by one user? all messages in a certain time window?) and be sure that these queries are approximately O(log N) rather than O(N) or worse. You will need to change your database design to fit the queries that you'll be doing. O(log N) queries would generally be characterized by the fields you're searching or sorting on being key fields (i.e. MySQL makes an index or BTREE or whatever it makes rather than having to go through the entire table linearly). The second element is data presentation. In developing web applications at least, if the first data on a page is displayed by the browser while the rest of the data is loading, the user perceives the load as being faster than it really is because the user is looking at the first data while the rest is loading. So, to make things more snappy, you might do more than one query to avoid large result sets. Dave.
Re: General DB Design Question - How to avoid redundancy in table relationships
Scott Klarenbach wrote: These are the tables in question: RFQ (Request for Quote) Part Inventory Inventory items ALWAYS have a partID. RFQ items ALWAYS have a partID. However, sometimes, RFQ items have an inventoryID as well. Now, we have a redundancy problem. Because, in those instances when the RFQ has an inventoryID, the partID should be derived from the inventoryID. If there is no inventoryID, then the partID needs to be stored directly in the RFQ table. We don't want to have both the inventoryID and the partID in the RFQ table, because it opens up data integrity issues. ie, what if the RFQ item shows inventoryID 2, and partID 1...but inventoryID 2 is associated to partID 2. Now which partID is correct? They can't both be right. Since there have not been a lot of responses I decided to jump in. It sounds to me like we have real-world object behavior mixed up with the data model. Example RFQ items ALWAYS have a partID If there is no inventoryID, then the partID needs to be stored directly in the RFQ table. Why? Who or what is enforcing this? Can we look at overall object behavior, then come up with a model that supports the behavior with no preconceptions of table structure. I assume that an RFQ item is a document (paper or eletronic). What does one look like? From your description it will always have a partID and may have an inventoryID. Who populates these fields? Why is there a redundancy in the first place? Who checks to see that the direct partID matches the derived partID? How about leaving partID and inventoryID out of the RFQ table, and adding an association table that relates a RFQ to either a partID or an inventoryID. An attribute of this table would distinguish partID from an inventoryID. Business logic would ensure that only one entry gets into this table per RFQ, and could also validate that the direct partID matches the derived partID I'm sure this type of problem is run up against all the time, and I'm wondering what the best practice methodology is from experienced DBA's. This was a simple example; however, we are running into the problem system wide. For example, a quote table has an OPTIONAL RFQ ID, and a mandatory contactID. The RFQ table has a mandatory contactID. If the quote table has an RFQID, we want to derive the contactID from the RFQID. If the quote has NO RFQID, then we need to store the contactID directly in the quote table. In those instances where there IS an RFQID in the quote table, we end up storing the contactID twice. Once in the quote table, and once in the association between the RFQ/Contact table. Same problem as above: integrity and poor overall design. Same issue here. Remove the IDs from the quote and RFQ table and create another association table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General DB Design Question - How to avoid redundancy in table relationships
These are the tables in question: RFQ (Request for Quote) Part Inventory Inventory items ALWAYS have a partID. RFQ items ALWAYS have a partID. However, sometimes, RFQ items have an inventoryID as well. Now, we have a redundancy problem. Because, in those instances when the RFQ has an inventoryID, the partID should be derived from the inventoryID. If there is no inventoryID, then the partID needs to be stored directly in the RFQ table. We don't want to have both the inventoryID and the partID in the RFQ table, because it opens up data integrity issues. ie, what if the RFQ item shows inventoryID 2, and partID 1...but inventoryID 2 is associated to partID 2. Now which partID is correct? They can't both be right. I'm sure this type of problem is run up against all the time, and I'm wondering what the best practice methodology is from experienced DBA's. This was a simple example; however, we are running into the problem system wide. For example, a quote table has an OPTIONAL RFQ ID, and a mandatory contactID. The RFQ table has a mandatory contactID. If the quote table has an RFQID, we want to derive the contactID from the RFQID. If the quote has NO RFQID, then we need to store the contactID directly in the quote table. In those instances where there IS an RFQID in the quote table, we end up storing the contactID twice. Once in the quote table, and once in the association between the RFQ/Contact table. Same problem as above: integrity and poor overall design. Thanks for your advice.
Re: General DB Design Question - How to avoid redundancy in table relationships
Scott, I'm sure this type of problem is run up against all the time, and I'm wondering what the best practice methodology is from experienced DBA's. It looks like the kind of problem database schemas are meant to _avoid_. >From your description it seems you have ... part ( partID PRIMARY KEY ) inventory ( inventoryID PRIMARY KEY, partID FOREIGN KEY REFERENCES part.partID ) RFQ ( rfqID PRIMARY KEY??? (I assume), partID FOREIGN KEY references part.partID, inventoryID NULL LOOKS UP inventory.inventoryID ) according to which ... (i) a RFQ item can reference a partID which is not in inventory, (ii) even if a RFQ partID is in inventory, it may show up in RFQ paired with a different invcentoryID, but (iii) if [ii] occurs, it indicates an error which is plumb crazy--if [ii] is an error, the schema should disallow it. The business rules embedded in this schema contain a contradiction. If it were my project, I'd conclude that it's time to sit down with the client. But perhaps we need more info? PB Scott Klarenbach wrote: These are the tables in question: RFQ (Request for Quote) Part Inventory Inventory items ALWAYS have a partID. RFQ items ALWAYS have a partID. However, sometimes, RFQ items have an inventoryID as well. Now, we have a redundancy problem. Because, in those instances when the RFQ has an inventoryID, the partID should be derived from the inventoryID. If there is no inventoryID, then the partID needs to be stored directly in the RFQ table. We don't want to have both the inventoryID and the partID in the RFQ table, because it opens up data integrity issues. ie, what if the RFQ item shows inventoryID 2, and partID 1...but inventoryID 2 is associated to partID 2. Now which partID is correct? They can't both be right. I'm sure this type of problem is run up against all the time, and I'm wondering what the best practice methodology is from experienced DBA's. This was a simple example; however, we are running into the problem system wide. For example, a quote table has an OPTIONAL RFQ ID, and a mandatory contactID. The RFQ table has a mandatory contactID. If the quote table has an RFQID, we want to derive the contactID from the RFQID. If the quote has NO RFQID, then we need to store the contactID directly in the quote table. In those instances where there IS an RFQID in the quote table, we end up storing the contactID twice. Once in the quote table, and once in the association between the RFQ/Contact table. Same problem as above: integrity and poor overall design. Thanks for your advice. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 2/13/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 2/13/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table design question
Hi, A very simple question. I have two products at our website and i would like to keep track of how many of each softwares were downloaded daily. I am planning to create the following table: id - auto_incr date_of_download - data product_name - enum value containing the two products Then I plan to use: select date_of_download, product_name, count(*) from table table group by 1,2 Any other faster method or design? Karam __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb design question
Greetings list, In an analysis I was sent, there is 1 table with a simple set of rules, e.g. a_table (id, day, max_hours, min_hours, min_days, start_time, stop_time, max_attendants, ...) No biggie there. The problem is the user has to be able to define exceptions to those rules. These exceptions will at one time be a quantitative value, i.e. a minimum or maximum number of attendants for a specific day, at other times it will concern a start_time that has to be met at least X times a month, etc. I've more or less broken the table structure for this down to a couple of tables, like this: table_case_quantity table_case_actions table_case_day table_case_time ... I 'concatenated' (foreign keys) related tables together into tables that specify a certain condition. At this time, there are 3 kinds of exception tables. In other words, I've managed to normalize this, no problem there. And now for the question :) When looking up exceptions, I would like it if there is just 1 table 'exceptions', that looks like this: exceptions (id, exception_type, id_of_the_exception_in_the_table_of_that_exception_type), i.e. while keeping a foreign constraint. In bashed up SQL syntax, I guess what I'd like to do is FOREIGN KEY (exception_type) REFERENCES table_of_the_exception_type (--- notice no id, just the table) FOREIGN KEY (id_of_the_...) REFERENCES table_of_the_exception_type(id) I'm thinking this has come up earlier, the question is how to do it. The other option is querying the db for every exception-type-table, which I'd prefer not doing. TIA, Stijn Verholen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Product Table Design Question
Hi All, I'm not sure the best design approach for a product table for a number of different hardware devices. Some devices have IP, Port, CPU, Memory specs, whilst some don't. Current desing is below. Products: product_id product_name maker_id controller_id product_type_id product_model_number product_serial_number product_age condition_id product_price product_sold_price product_sold product_auctioned product_qty product_last_updated product_data_output product_desc ProductTypes: product_type_id product_type_detail Makers: maker_id maker_detail Some products we'll have are, switches/routers/dedicated servers/firewalls etc. Should I just make a Specs table, or, specific table for each type of product.? Appreciate any thoughts on this. Cheers. Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Product Table Design Question
Mark Sargent [EMAIL PROTECTED] wrote on 06/03/2005 03:04:23 AM: Hi All, I'm not sure the best design approach for a product table for a number of different hardware devices. Some devices have IP, Port, CPU, Memory specs, whilst some don't. Current desing is below. Products: product_id product_name maker_id controller_id product_type_id product_model_number product_serial_number product_age condition_id product_price product_sold_price product_sold product_auctioned product_qty product_last_updated product_data_output product_desc ProductTypes: product_type_id product_type_detail Makers: maker_id maker_detail Some products we'll have are, switches/routers/dedicated servers/firewalls etc. Should I just make a Specs table, or, specific table for each type of product.? Appreciate any thoughts on this. Cheers. Mark Sargent. In my opinion, you have identified the core descriptors for any product already (your Products table). Now what you need to be able to provide is a list of descriptors and their values: (# of ports, 16), (# of cpus,2), (# expansion bays,6), (expansion option 1, TELCO-card), etc That kind of flexible design (where you are not limited to the number of extra items you can tack onto a Product) fits best into it's own, vertically organized table (ProductFeatures, below) CREATE TABLE Features ( feature_id int auto_increment primary key , feature varchar(75) not null , UNIQUE (feature) ) ENGINE=InnoDB; CREATE TABLE ProductFeatures ( prodfeat_id int auto_increment primary key , product_id int not null , feature_id int not null , value varchar(255) , UNIQUE(product_id, feature_id) , KEY(feature_id) , FOREIGN KEY (product_ID) References Products(product_id) , FOREIGN KEY (feature_ID) References Features(feature_id) ) ENGINE=InnoDB; Just to keep your data (and your GUI design) more manageable, I normalized the list of possible features into their own table. That way you don't have someone typing in # of ports for one product and port count for another. The UNIQUE index on the ProductFeatures should help to prevent having more than 1 of the same feature described for the same product. The FK constraints prevents you from adding non-existent features to a product that may or may not exist. Just my 2 cents... Shawn Green Database Administrator Unimin Corporation - Spruce Pine
DB design question
Hi, here is the case: one student may have more than one address, and one student may have more than one phone number so the db would be: student student_id name age address --- address_id student_id street_name phone_num -- student_id num extension the key of 3 tables are student_id the problems is, when I want to query both student, address and phone num, the sql will be select * from student s, address a, phone_num n where s.student_id = a.sudent_id and s.student_id = n.student_id it won't provide a nice result as data of student are repeated in every row, address and phone_num's data are repeated in certain rows The output is not suitable for reporting and may I ask what is the better way of design to handle the above case ? any help would be apreciated Regards
RE: DB design question
Something like this would make more sense to me and provide greater flexibility; student student_id name age address --- address_id street_name city state zip phone_num -- phone_num_id num extension type (cell, home, etc) primaryNumber (yes/no) student_info --- student_id_FK phone_num_id_FK address_id_FK Spent all of 10 mins on this so its not perfect. Bottom line is I would not include the student_id in the address and phone tables. It precludes a student having multiple phones or addresses with out duplicate data. The addition of the student_info table provide the 1:1 or 1:N mapping you're looking for I believe. The only thing you need to ensure is properly set the Cascade on update and restrict on delete options to ensure data integrity. My gut tells me it may be a better implementation to map the student/phone and student/address separately and then create the student_info using keys from these intermediate tables, but it more complicated and it not clear what the constraints on your problem is. Bob Bartis -Original Message- From: Koon Yue Lam [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 24, 2005 1:34 PM To: mysql@lists.mysql.com Subject: DB design question Hi, here is the case: one student may have more than one address, and one student may have more than one phone number so the db would be: student student_id name age address --- address_id student_id street_name phone_num -- student_id num extension the key of 3 tables are student_id the problems is, when I want to query both student, address and phone num, the sql will be select * from student s, address a, phone_num n where s.student_id = a.sudent_id and s.student_id = n.student_id it won't provide a nice result as data of student are repeated in every row, address and phone_num's data are repeated in certain rows The output is not suitable for reporting and may I ask what is the better way of design to handle the above case ? any help would be apreciated Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB design question
Koon Yue Lam, If you running your MySQL on Windows, you may try to use one of the reporting tools, like Crystal Report, to create your reports. Generally these tools allow to hide repetitive data in its reports Mikhail Berman -Original Message- From: Koon Yue Lam [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 24, 2005 1:34 PM To: mysql@lists.mysql.com Subject: DB design question Hi, here is the case: one student may have more than one address, and one student may have more than one phone number so the db would be: student student_id name age address --- address_id student_id street_name phone_num -- student_id num extension the key of 3 tables are student_id the problems is, when I want to query both student, address and phone num, the sql will be select * from student s, address a, phone_num n where s.student_id = a.sudent_id and s.student_id = n.student_id it won't provide a nice result as data of student are repeated in every row, address and phone_num's data are repeated in certain rows The output is not suitable for reporting and may I ask what is the better way of design to handle the above case ? any help would be apreciated Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB design question
From: Koon Yue Lam [mailto:[EMAIL PROTECTED] Hi, here is the case: one student may have more than one address, and one student may have more than one phone number so the db would be: student student_id name age address --- address_id student_id street_name phone_num -- student_id num extension the key of 3 tables are student_id the problems is, when I want to query both student, address and phone num, the sql will be select * from student s, address a, phone_num n where s.student_id = a.sudent_id and s.student_id = n.student_id it won't provide a nice result as data of student are repeated in every row, address and phone_num's data are repeated in certain rows The output is not suitable for reporting and may I ask what is the better way of design to handle the above case ? It's good DB design, but you need to not `select *' but the specific fields you'd like. An example of might be: SELECT s.name, s.age, a.street_name, n.num, n.extension FROM students s JOIN address a ON a.student_id = s.student_id JOIN phone_num n ON n.student_id = s.student_id HTH! -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB design question
From: Mike Johnson [mailto:[EMAIL PROTECTED] From: Koon Yue Lam [mailto:[EMAIL PROTECTED] the problems is, when I want to query both student, address and phone num, the sql will be select * from student s, address a, phone_num n where s.student_id = a.sudent_id and s.student_id = n.student_id it won't provide a nice result as data of student are repeated in every row, address and phone_num's data are repeated in certain rows The output is not suitable for reporting and may I ask what is the better way of design to handle the above case ? It's good DB design, but you need to not `select *' but the specific fields you'd like. An example of might be: SELECT s.name, s.age, a.street_name, n.num, n.extension FROM students s JOIN address a ON a.student_id = s.student_id JOIN phone_num n ON n.student_id = s.student_id I just realized I sort of misread your question (or, rather, only read the first half of it). I guess my question is whether or not you're using some sort of front-end scripting language to retrieve results or using the MySQL client straight. If the former, you can definitely work with the data in the way you'd like, but as for the latter, the MySQL client itself wasn't actually meant to be used as any sort of reporting tool. It certainly isn't made to make data look nice. :) Might you be using PHP, Perl, or something else like that? -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB design question
Something like this would make more sense to me and provide greater flexibility; It doesn't to me... student student_id name age address --- address_id street_name city state zip What addresses are these? Random addresses where a student _might_ live? phone_num -- phone_num_id num extension type (cell, home, etc) primaryNumber (yes/no) Again, random phone numbers possibily owned by a student? student_info --- student_id_FK phone_num_id_FK address_id_FK If an address isn't any address, why doesn't it relate to a student? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB design question
You probably want to add type to both the address and phone tables. Then you can be selective in your reporting and still get 1 row per student in your result set. Just remember if your data has the possibility of not having the information for a student you want to use LEFT JOIN's vs INNER JOIN's or the student with no primary phone [in the following statement] will not be included in the result set. SELECT student_id, name, age, h.street_name AS home_address, s.street name AS school_address, n.num AS primary_phone FROM student s LEFT JOIN address s USING (student_id) LEFT JOIN address h USING (student_id) INNER JOIN phone_num n USING (student_id) WHERE h.type = 'Home' AND s.type = 'School' AND n.type = 'Primary' -Original Message- From: Koon Yue Lam [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 24, 2005 12:34 PM To: mysql@lists.mysql.com Subject: DB design question Hi, here is the case: one student may have more than one address, and one student may have more than one phone number so the db would be: student student_id name age address --- address_id student_id street_name phone_num -- student_id num extension the key of 3 tables are student_id the problems is, when I want to query both student, address and phone num, the sql will be select * from student s, address a, phone_num n where s.student_id = a.sudent_id and s.student_id = n.student_id it won't provide a nice result as data of student are repeated in every row, address and phone_num's data are repeated in certain rows The output is not suitable for reporting and may I ask what is the better way of design to handle the above case ? any help would be apreciated Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB design question
Martijn Tonies [EMAIL PROTECTED] wrote on 05/24/2005 02:32:05 PM: Something like this would make more sense to me and provide greater flexibility; It doesn't to me... student student_id name age address --- address_id street_name city state zip What addresses are these? Random addresses where a student _might_ live? Not necessarily random but yes, those would be addresses. phone_num -- phone_num_id num extension type (cell, home, etc) primaryNumber (yes/no) Again, random phone numbers possibily owned by a student? Yes. Again, not necessarily random. student_info --- student_id_FK phone_num_id_FK address_id_FK I think this table works well because most phone numbers are linked with an address. If the student has two addresses (a home address and a school address) and 4 phone numbers (two home phone numbers, a school phone, and a cell phone), there would need to be 4 records added to this table. The data would look something like this: student_id, address_id, phone_num_id 4,2,15 4,2,16 4,13,22 4,41,89 If an address isn't any address, why doesn't it relate to a student? Odds are, if an address is not related to at least one student, it wouldn't exist in the data. However, imagine you have been asked to build a student finder database for a university. It should be practical to pre-load your database with all of the addresses of the on-campus housing (all known student addresses). In that case you could have several dozen address records in your database before adding any student records at all. The relevance of the address records is not apparent if you just look only at the structure. Rather it comes from the choice of the data you populate the tables with. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Koon Yue Lam: To repeat what others have said, the query will correctly return repetitive information for your student fields if there is more than one address or phone number or some combination of either per student. Data retrieval tools are generally not intended to present hierarchical information in a hierarchical manner. That is generally accomplished with data analysis tools or data presentation tools or user-written code. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: DB design question
Shawn, I agree with you that the tables can have different info with regard to the requirements. But for storing only addresses for specific students, this 4 table design seems weirdish to me... I think it makes more sense to keep a student_id in the Addresses table... With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Something like this would make more sense to me and provide greater flexibility; It doesn't to me... student student_id name age address --- address_id street_name city state zip What addresses are these? Random addresses where a student _might_ live? Not necessarily random but yes, those would be addresses. phone_num -- phone_num_id num extension type (cell, home, etc) primaryNumber (yes/no) Again, random phone numbers possibily owned by a student? Yes. Again, not necessarily random. student_info --- student_id_FK phone_num_id_FK address_id_FK I think this table works well because most phone numbers are linked with an address. If the student has two addresses (a home address and a school address) and 4 phone numbers (two home phone numbers, a school phone, and a cell phone), there would need to be 4 records added to this table. The data would look something like this: student_id, address_id, phone_num_id 4,2,15 4,2,16 4,13,22 4,41,89 If an address isn't any address, why doesn't it relate to a student? Odds are, if an address is not related to at least one student, it wouldn't exist in the data. However, imagine you have been asked to build a student finder database for a university. It should be practical to pre-load your database with all of the addresses of the on-campus housing (all known student addresses). In that case you could have several dozen address records in your database before adding any student records at all. The relevance of the address records is not apparent if you just look only at the structure. Rather it comes from the choice of the data you populate the tables with. Koon Yue Lam: To repeat what others have said, the query will correctly return repetitive information for your student fields if there is more than one address or phone number or some combination of either per student. Data retrieval tools are generally not intended to present hierarchical information in a hierarchical manner. That is generally accomplished with data analysis tools or data presentation tools or user-written code. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
database design question
I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords in these tables...and then retrieve the saved information from the four different tables. Question: Should I just search each of the tables individually? Or should I create another table that will hold the keywords, the tablename, and the ID of the saved record in that particular table...and then perform my search on this NEW table? Thanks. -- -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database design question
james tu [EMAIL PROTECTED] wrote on 04/26/2005 12:06:34 PM: I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords in these tables...and then retrieve the saved information from the four different tables. Question: Should I just search each of the tables individually? Or should I create another table that will hold the keywords, the tablename, and the ID of the saved record in that particular table...and then perform my search on this NEW table? Thanks. -- -James I would properly index each table and UNION the results of the 4 searches. Have you considered creating a Full Text index for your keyword fields? Say your 4 tables are called: running, swimming, jumping, and walking SELECT 'running', column list FROM running WHERE keywords search condition UNION SELECT 'swimming', column list FROM swimming WHERE keywords search condition UNION SELECT 'jumping', column list FROM jumping WHERE keywords search condition UNION SELECT 'walking', column list FROM walking WHERE keywords search condition; I used the first column only to identify which table each match comes from. That way if you have records in each table with matching PK values, you know which table to go back to in order to get any additional information. The only problem with this type of search is that your column list columns must be compatible between each of the tables. If the second column is numeric in your first query then the second column will be coerced to numeric for each of the remaining 3 queries. If for some reason that fails, then the whole UNION fails and you get an error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: database design question
I tried that and maybe I'm doing something wrong but... -I have to select the same number of columns...for each UNION -And each of the records from the union fall under the same column headings as the first SELECT... I even tried to define column aliases.. SELECT `running` as `running_blah`... -James At 1:03 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: james tu [EMAIL PROTECTED] wrote on 04/26/2005 12:06:34 PM: I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords in these tables...and then retrieve the saved information from the four different tables. Question: Should I just search each of the tables individually? Or should I create another table that will hold the keywords, the tablename, and the ID of the saved record in that particular table...and then perform my search on this NEW table? Thanks. -- -James I would properly index each table and UNION the results of the 4 searches. Have you considered creating a Full Text index for your keyword fields? Say your 4 tables are called: running, swimming, jumping, and walking SELECT 'running', column list FROM running WHERE keywords search condition UNION SELECT 'swimming', column list FROM swimming WHERE keywords search condition UNION SELECT 'jumping', column list FROM jumping WHERE keywords search condition UNION SELECT 'walking', column list FROM walking WHERE keywords search condition; I used the first column only to identify which table each match comes from. That way if you have records in each table with matching PK values, you know which table to go back to in order to get any additional information. The only problem with this type of search is that your column list columns must be compatible between each of the tables. If the second column is numeric in your first query then the second column will be coerced to numeric for each of the remaining 3 queries. If for some reason that fails, then the whole UNION fails and you get an error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- -James Tu --- ESI Design 111 Fifth Avenue 12th floor New York, NY 10003 (212) 989-3993 ext. 357 (212) 673-4061 (fax) ---
Re: database design question
If you posted your actual table structures (SHOW CREATE TABLE xx\G) I think I could be more helpful. Right now I am just shooting in the dark. Shawn Green Database Administrator Unimin Corporation - Spruce Pine James [EMAIL PROTECTED] wrote on 04/26/2005 02:15:49 PM: I tried that and maybe I'm doing something wrong but... -I have to select the same number of columns...for each UNION -And each of the records from the union fall under the same column headings as the first SELECT... I even tried to define column aliases.. SELECT `running` as `running_blah`... -James At 1:03 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: james tu [EMAIL PROTECTED] wrote on 04/26/2005 12:06:34 PM: I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords in these tables...and then retrieve the saved information from the four different tables. Question: Should I just search each of the tables individually? Or should I create another table that will hold the keywords, the tablename, and the ID of the saved record in that particular table...and then perform my search on this NEW table? Thanks. -- -James I would properly index each table and UNION the results of the 4 searches. Have you considered creating a Full Text index for your keyword fields? Say your 4 tables are called: running, swimming, jumping, and walking SELECT 'running', column list FROM running WHERE keywords search condition UNION SELECT 'swimming', column list FROM swimming WHERE keywords search condition UNION SELECT 'jumping', column list FROM jumping WHERE keywords search condition UNION SELECT 'walking', column list FROM walking WHERE keywords search condition; I used the first column only to identify which table each match comes from. That way if you have records in each table with matching PK values, you know which table to go back to in order to get any additional information. The only problem with this type of search is that your column list columns must be compatible between each of the tables. If the second column is numeric in your first query then the second column will be coerced to numeric for each of the remaining 3 queries. If for some reason that fails, then the whole UNION fails and you get an error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- -James Tu --- ESI Design 111 Fifth Avenue 12th floor New York, NY 10003 (212) 989-3993 ext. 357 (212) 673-4061 (fax) ---
Re: database design question
I haven't created real project tables yet. But here are the test ones that I'm experimenting with. CREATE TABLE east ( id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, east_1 varchar(255) default NULL, PRIMARY KEY (id) ) ; CREATE TABLE north ( north_id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, north_1 varchar(255) default NULL, north_2 varchar(255) default NULL, north_3 varchar(255) default NULL, PRIMARY KEY (north_id) ) ; CREATE TABLE south ( id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, south_1 varchar(255) default NULL, south_2 varchar(255) default NULL, south_3 varchar(255) default NULL, timestamp timestamp(14) NOT NULL, PRIMARY KEY (id) ) ; CREATE TABLE west ( west_id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, west_1 varchar(255) default NULL, PRIMARY KEY (west_id) ); I want to search on the keywords in all of these tables and retrieve the records from each table that fits the WHERE clause. The question is...should I just: (1) Make four queries and programmatically keep track of the results from each table? ...or (2) Create another table (let's call it `keywords`) and pull out the keywords into this new table...and store an ID that exists in north,south, east, west...and also store a column that tells us which table this ID is from?...Then we do a query on this table? I guess either way I would have to programmatically at some point fetch with four queries... At 2:46 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: If you posted your actual table structures (SHOW CREATE TABLE xx\G) I think I could be more helpful. Right now I am just shooting in the dark. Shawn Green Database Administrator Unimin Corporation - Spruce Pine James [EMAIL PROTECTED] wrote on 04/26/2005 02:15:49 PM: I tried that and maybe I'm doing something wrong but... -I have to select the same number of columns...for each UNION -And each of the records from the union fall under the same column headings as the first SELECT... I even tried to define column aliases.. SELECT `running` as `running_blah`... -James At 1:03 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: james tu [EMAIL PROTECTED] wrote on 04/26/2005 12:06:34 PM: I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords in these tables...and then retrieve the saved information from the four different tables. Question: Should I just search each of the tables individually? Or should I create another table that will hold the keywords, the tablename, and the ID of the saved record in that particular table...and then perform my search on this NEW table? Thanks. -- -James I would properly index each table and UNION the results of the 4 searches. Have you considered creating a Full Text index for your keyword fields? Say your 4 tables are called: running, swimming, jumping, and walking SELECT 'running', column list FROM running WHERE keywords search condition UNION SELECT 'swimming', column list FROM swimming WHERE keywords search condition UNION SELECT 'jumping', column list FROM jumping WHERE keywords search condition UNION SELECT 'walking', column list FROM walking WHERE keywords search condition; I used the first column only to identify which table each match comes from. That way if you have records in each table with matching PK values, you know which table to go back to in order to get any additional information. The only problem with this type of search is that your column list columns must be compatible between each of the tables. If the second column is numeric in your first query then the second column will be coerced to numeric for each of the remaining 3 queries. If for some reason that fails, then the whole UNION fails and you get an error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- -James Tu --- ESI Design 111 Fifth Avenue 12th floor New York, NY 10003 (212) 989-3993 ext. 357 (212) 673-4061 (fax) --- -- -James Tu --- ESI Design 111 Fifth Avenue 12th floor New York, NY 10003 (212) 989-3993 ext. 357 (212) 673-4061 (fax) ---
Database design question
Hello, I have two questions: I would like to know whether I am violating the principle of atomicity in doing the following: I have a form which has a field with the following options: Choice1, choice2 and choice3. Each of the above choices have two further subchoices : subChoice1, subchoice2. so in my form I have a pull-down list and these are the choices I have: choice1-subchoice1 choice1-subchoice2 choice2-subchoice1 and so on. Are these values atomical? My other question is what are the repercussions of not putting a table in 2nd and 3rd Normal Form. Thank you. -- -Mahmoud Badreddine
Re: Database design question
Mahmoud, Are these values atomical? My other question is what are the repercussions of not putting a table in 2nd and 3rd Normal Form. Your 'choice1-subchoice1' etc are combined values, so they aren't atomic. From your three example dropdown values, it looks as if 'choice' and 'subchoice' have a many-to-many relationship, and if that's so, you'll probably need a second table for 'subchoice' and a third table to store combinations of choice and subschoice. There are examples of disadvantages of not putting a table in 2NF or 3NF at http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf, click on 'Normalisation and the normal forms', read the sections on 1NF, 2NF and 3NF. PB - Mahmoud Badreddine wrote: Hello, I have two questions: I would like to know whether I am violating the principle of atomicity in doing the following: I have a form which has a field with the following options: Choice1, choice2 and choice3. Each of the above choices have two further subchoices : subChoice1, subchoice2. so in my form I have a pull-down list and these are the choices I have: choice1-subchoice1 choice1-subchoice2 choice2-subchoice1 and so on. Are these values atomical? My other question is what are the repercussions of not putting a table in 2nd and 3rd Normal Form. Thank you. No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.10 - Release Date: 4/14/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.10 - Release Date: 4/14/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A database design question
Hi all, I have some doubts about how to implement this kind of scenario: I have a table of elements in cronological order, let's call it table A: for every A element, I have a number of elements ordered on a progressive number. This could be a simply one-to-many relation, where I can handle a list of all A records with related B records using a left join. the issue is complicated ( for me, at least ) by the fact that the records related to table A can be of two different types, that have in common some fields but not others. I mean for every record A I have an ordered list of mixed records B and C. So I'm thinking about pro and cons of three different ways to handle this problem. 1) create tables A,B, and C, with tables B and C having a field id_A containing the ID of records A they belong, and figure out how to handle a left join having oh its right side elements from two different tables 2) create tables A,B, and C, and create an intermediate table D to link table A elements with their related B and C elements, and again figure out how to handle the list of A elements with linked B and C elements. 3) create only tables A and D, where table D is a mix of the fields from tables B and C with added a fileld rec_type to handle different fields depending on the record type ( this seems to me to be the simplest solution, although not the best in term of normalization rules ) Hope it was all clear, thanx in advance, Giulio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A database design question
I think you need to explain what kind of SELECTs you want to do, and what results you expect. How do you expect to get results from a SELECT which returns hits in both the B and C tables? If you expect to do this, then the D table is probably your correct answer. Do you really need a rec_type field? Can you not leave the columns which exist only in B type records null in c-type records and vice versa? How much commonality is there between B and C type fields? I presume there is some, or you would not be wanting to merge them. Incidentally, I think you only need a simple join, not a left join - unless I misunderstand. Alec Giulio [EMAIL PROTECTED] wrote on 11/10/2004 10:44:43: Hi all, I have some doubts about how to implement this kind of scenario: I have a table of elements in cronological order, let's call it table A: for every A element, I have a number of elements ordered on a progressive number. This could be a simply one-to-many relation, where I can handle a list of all A records with related B records using a left join. the issue is complicated ( for me, at least ) by the fact that the records related to table A can be of two different types, that have in common some fields but not others. I mean for every record A I have an ordered list of mixed records B and C. So I'm thinking about pro and cons of three different ways to handle this problem. 1) create tables A,B, and C, with tables B and C having a field id_A containing the ID of records A they belong, and figure out how to handle a left join having oh its right side elements from two different tables 2) create tables A,B, and C, and create an intermediate table D to link table A elements with their related B and C elements, and again figure out how to handle the list of A elements with linked B and C elements. 3) create only tables A and D, where table D is a mix of the fields from tables B and C with added a fileld rec_type to handle different fields depending on the record type ( this seems to me to be the simplest solution, although not the best in term of normalization rules ) Hope it was all clear, thanx in advance, Giulio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A database design question
Il giorno 11/ott/04, alle 11:53, [EMAIL PROTECTED] ha scritto: I think you need to explain what kind of SELECTs you want to do, and what results you expect. you're right, I'll try to explain it better I'm working on a system that must keep track of all the music broadcasted by a tv, so, let's call record A TVprogram, TVprogram table will contain all the programs broadcasted in cronological order, they have a broadcasting date, start time and end time, type of program ( news, entertainment, cartoons, and so on... ) every TVprogram record can have one or more associated records of two types: a record B ( let's call it MusicTrack ), containg info ( title, composer, etc. ) about a Music track used on some way in the TV program a record C ( let's call it Movie ), containing info ( title, director, etc. ) about a movie or serial or cartoon and so on broadcasted during the TV program. ( the list of music tracks used on the movie will be extracted from another database at later time). MusicTrack and Movie are associated to a given TVprogram on a progressive ( and cronological ) order. So I.E., for a TVprogram record I could have: 1 a MusicTrack record with info about a song used as intro for the program 2 a MusicTrack record with info about a song used as background music while talking about the movie that will be broadcasted 3 a Movie record containing info about the movie itself 4 a MusicTrack record with info about a song used at the end of the program the select I would like to perform is, given a TVprogram element, have a list of all its MusicTrack or Movie records in crological order, or have a list of TVprogram elements on a given interval, and for everyone of them a list of their referred records. but you're right, I now think the possible solutions are to merge the two table type on one table type, or keep them separated, perform two different separate joins and then merge them by code... thank you, Giulio How do you expect to get results from a SELECT which returns hits in both the B and C tables? If you expect to do this, then the D table is probably your correct answer. Do you really need a rec_type field? Can you not leave the columns which exist only in B type records null in c-type records and vice versa? How much commonality is there between B and C type fields? I presume there is some, or you would not be wanting to merge them. Incidentally, I think you only need a simple join, not a left join - unless I misunderstand. Alec Giulio [EMAIL PROTECTED] wrote on 11/10/2004 10:44:43: Hi all, I have some doubts about how to implement this kind of scenario: I have a table of elements in cronological order, let's call it table A: for every A element, I have a number of elements ordered on a progressive number. This could be a simply one-to-many relation, where I can handle a list of all A records with related B records using a left join. the issue is complicated ( for me, at least ) by the fact that the records related to table A can be of two different types, that have in common some fields but not others. I mean for every record A I have an ordered list of mixed records B and C. So I'm thinking about pro and cons of three different ways to handle this problem. 1) create tables A,B, and C, with tables B and C having a field id_A containing the ID of records A they belong, and figure out how to handle a left join having oh its right side elements from two different tables 2) create tables A,B, and C, and create an intermediate table D to link table A elements with their related B and C elements, and again figure out how to handle the list of A elements with linked B and C elements. 3) create only tables A and D, where table D is a mix of the fields from tables B and C with added a fileld rec_type to handle different fields depending on the record type ( this seems to me to be the simplest solution, although not the best in term of normalization rules ) Hope it was all clear, thanx in advance, Giulio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Cantoberon Multimedia srl http://www.cantoberon.it Tel. 06 39737052 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A database design question
My answers interspersed below (and yes, I have read his follow up reply that had additional information) Giulio [EMAIL PROTECTED] wrote on 10/11/2004 05:44:43 AM: Hi all, I have some doubts about how to implement this kind of scenario: I have a table of elements in cronological order, let's call it table A: for every A element, I have a number of elements ordered on a progressive number. This could be a simply one-to-many relation, where I can handle a list of all A records with related B records using a left join. the issue is complicated ( for me, at least ) by the fact that the records related to table A can be of two different types, that have in common some fields but not others. I mean for every record A I have an ordered list of mixed records B and C. So I'm thinking about pro and cons of three different ways to handle this problem. 1) create tables A,B, and C, with tables B and C having a field id_A containing the ID of records A they belong, and figure out how to handle a left join having oh its right side elements from two different tables 2) create tables A,B, and C, and create an intermediate table D to link table A elements with their related B and C elements, and again figure out how to handle the list of A elements with linked B and C elements. I would think that this structure (#2) would fit your model the best. Each Table A element could contain various elements of tables B and C depending on the schedule/format of that show. Table D would be your logging table that would relate elements of Table A to elements of Tables B or C along with a time stamp (based on what time in the program the element appeared.) I have seen many databases designed with a table like D that needed to contain references to objects of different types. I would create tableD to look something like this: CREATE TABLE content_log ( ID int auto_increment primary key, tableA_id int not null, Object_ID int not null, Object_type (here you have options, you could use a SET, ENUM, INT, or CHAR datatype), TimeOffset time not null ) 3) create only tables A and D, where table D is a mix of the fields from tables B and C with added a fileld rec_type to handle different fields depending on the record type ( this seems to me to be the simplest solution, although not the best in term of normalization rules ) Hope it was all clear, thanx in advance, Giulio So.. if you wanted to find all of the movies shown during Show # 14 SELECT * FROM content_log WHERE tableA_ID = 14 and object_type='movie' /*or however you set up that column*/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Simple DB design question
I have some data that is stored by the year it is related to. So I have one
table that stores the Year the data is related to, among other things. At any
given time, 1 year is considered the 'active year', and the rest are
considered inactive.
The table is something like:
CREATE TABLE Data_Info
Data_Info_IDINT,
YearINT,
...
So my question is how do i best store which year is active. 2 designs come to
mind:
1) add a column such as:
Status ENUM('Active', 'Inactive')
and adjust accordingly as the active year changes.
2) have a separate table:
CREATE TABLE Active_Data (
Data_Info_IDINT,
Key (Data_Info_ID),
FOREIGN KEY (Data_ID) References Data_Info(Data_Info_ID)
);
With design 1, i need to make sure that only 1 record is ever set as 'Active'.
With design 2, there will only ever be 1 record in the Active_Data table.
Neither idea seems very good to me.
Any suggestions?
thanks much
sean peters
[EMAIL PROTECTED]
mysql, query
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple DB design question
I've got a history table that performs a similar function. except in my
case I can have more than 1 active row. I put in an is_active column
and defined the type as a bool. an enum is actually a String in mysql,
which i didn't want to deal with. This table has only a few thousand
rows, so performance is not impacted at all.
jeff
sean c peters wrote:
I have some data that is stored by the year it is related to. So I have one
table that stores the Year the data is related to, among other things. At any
given time, 1 year is considered the 'active year', and the rest are
considered inactive.
The table is something like:
CREATE TABLE Data_Info
Data_Info_IDINT,
YearINT,
...
So my question is how do i best store which year is active. 2 designs come to
mind:
1) add a column such as:
Status ENUM('Active', 'Inactive')
and adjust accordingly as the active year changes.
2) have a separate table:
CREATE TABLE Active_Data (
Data_Info_IDINT,
Key (Data_Info_ID),
FOREIGN KEY (Data_ID) References Data_Info(Data_Info_ID)
);
With design 1, i need to make sure that only 1 record is ever set as 'Active'.
With design 2, there will only ever be 1 record in the Active_Data table.
Neither idea seems very good to me.
Any suggestions?
thanks much
sean peters
[EMAIL PROTECTED]
mysql, query
--
Jeff Mathis, Ph.D. 505-955-1434
Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Design Question
Hi all, I need some advice on a project I have. Basically, I have some tables: CREATE TABLE listings ( ListingID bigint(20) unsigned NOT NULL auto_increment, CatalogNumber varchar(12) NOT NULL default '', PDFLink varchar(100) default NULL, PDFName varchar(80) default NULL, Title varchar(100) NOT NULL default '', ComposerID int(11) default NULL, ArrangerID int(11) default NULL, PublisherID int(11) default NULL, Price double(16,2) NOT NULL default '0.00', DiscountID int(11) default NULL, Description text, NewTitles tinyint(1) default NULL, CategoryID int(11) NOT NULL default '0', PRIMARY KEY (ListingID) ) TYPE=MyISAM; CREATE TABLE categories ( CategoryID int(11) NOT NULL auto_increment, Name varchar(50) NOT NULL default '', Alias varchar(60) default NULL, DiscountID int(11) default NULL, Description text, GroupID int(11) NOT NULL default '0', PRIMARY KEY (CategoryID) ) TYPE=MyISAM; CREATE TABLE groups ( GroupID int(11) NOT NULL auto_increment, Name varchar(50) default NULL, DiscountID int(11) default NULL, PRIMARY KEY (GroupID) ) TYPE=MyISAM; Currently, there is a one-to-one relationship between listings and categories, and listings and groups. Now, the customer is requesting that a listing be included in several categories. I am not quite sure how to do this. My thought was to add a new field to the listings table that would contain a comma-separated list of CategoryIDs, but something doesn't feel right about this solution. What would be a good approach to this problem? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design Question
EB My thought was to add a new field to the listings table that would EB contain a comma-separated list of CategoryIDs, but something doesn't EB feel right about this solution. This would break the first normalization form and is extremely bad Okay - I thought something was off... First of all ask your customer - what is the relation between listings and categories - is it one-to-many or many-to-one or many-to-many relation The current relationship is one to one - each listing can only have one category. The customer is requesting a change to this, so that each listing can have many categories. if it is one-to-many (many-to-one) then you should add a field to details table that constitutes a primary key in the main table and define a foreign key. That means having either CategoryID in listings table or ListingID in categories table. My current table definition for the listings already has the foreign key of CategoryID. What you are saying is that the categories table should have a field for ListingID? Thanks! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design Question
You are right, a comma separated list won't work since you won't be able to do joins on it. To create a one to many relation, you actually need to create another table to hold the relation. CREATE TABLE listCatLink ( ListingID bigint(20) unsigned NOT NULL, CategoryID int(11) NOT NULL ) On Aug 4, 2004, at 10:35 AM, Erich Beyrent wrote: Hi all, I need some advice on a project I have. Basically, I have some tables: CREATE TABLE listings ( ListingID bigint(20) unsigned NOT NULL auto_increment, CatalogNumber varchar(12) NOT NULL default '', PDFLink varchar(100) default NULL, PDFName varchar(80) default NULL, Title varchar(100) NOT NULL default '', ComposerID int(11) default NULL, ArrangerID int(11) default NULL, PublisherID int(11) default NULL, Price double(16,2) NOT NULL default '0.00', DiscountID int(11) default NULL, Description text, NewTitles tinyint(1) default NULL, CategoryID int(11) NOT NULL default '0', PRIMARY KEY (ListingID) ) TYPE=MyISAM; CREATE TABLE categories ( CategoryID int(11) NOT NULL auto_increment, Name varchar(50) NOT NULL default '', Alias varchar(60) default NULL, DiscountID int(11) default NULL, Description text, GroupID int(11) NOT NULL default '0', PRIMARY KEY (CategoryID) ) TYPE=MyISAM; CREATE TABLE groups ( GroupID int(11) NOT NULL auto_increment, Name varchar(50) default NULL, DiscountID int(11) default NULL, PRIMARY KEY (GroupID) ) TYPE=MyISAM; Currently, there is a one-to-one relationship between listings and categories, and listings and groups. Now, the customer is requesting that a listing be included in several categories. I am not quite sure how to do this. My thought was to add a new field to the listings table that would contain a comma-separated list of CategoryIDs, but something doesn't feel right about this solution. What would be a good approach to this problem? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design Question
As posted, your data structure supports two one-to-many relationships, not the one-to-one relationships as you described. You can have multiple Listings per Category and multiple Categories per Group. What it sounds like you have been asked to do is to support a many-to-many relationship. You need to support both multiple Listings per Category and multiple Categories per Listing. As you have it now: Groups (1..*) Categories (1..*) Listings As you need it to be: Groups (1..*) Categories (*..*) Listings To create a (*..*) relationship between two tables, you need a third table. Each entry in this table represents one Listing-Category association (relationship). CREATE TABLE listings_projects ( ListingID bigint not null , CategoryID int , ... any additional fields as needed ... , PRIMARY KEY (ListingID, CategoryID) ) The primary key ensures that at each Listing/Category combination appears only once (no duplicate assignments). I showed you where additional fields can fit into the relation table because sometimes there are facts about relationships that do not fit into either of the tables they relate A recent example in this list was a relation table between chemical compounds and the various plants in which those compounds could be found. A fact that belongs to the *relationship* could be the concentration of that chemical in that plant. That concentration value would not belong to the plants table nor would it belong to the compounds table but does belong to the relationship of plant to compound. Make sense? I have personally used additional fields like those to indicate deletion. That way old values are no longer available for new relationships (in my applications) and my queries won't break as I didn't actually get rid of any information. My historical reports still function as the old names are still in the system, even if you can't use the the old names for any current purposes. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Erich Beyrent [EMAIL PROTECTED] wrote on 08/04/2004 10:35:33 AM: Hi all, I need some advice on a project I have. Basically, I have some tables: CREATE TABLE listings ( ListingID bigint(20) unsigned NOT NULL auto_increment, CatalogNumber varchar(12) NOT NULL default '', PDFLink varchar(100) default NULL, PDFName varchar(80) default NULL, Title varchar(100) NOT NULL default '', ComposerID int(11) default NULL, ArrangerID int(11) default NULL, PublisherID int(11) default NULL, Price double(16,2) NOT NULL default '0.00', DiscountID int(11) default NULL, Description text, NewTitles tinyint(1) default NULL, CategoryID int(11) NOT NULL default '0', PRIMARY KEY (ListingID) ) TYPE=MyISAM; CREATE TABLE categories ( CategoryID int(11) NOT NULL auto_increment, Name varchar(50) NOT NULL default '', Alias varchar(60) default NULL, DiscountID int(11) default NULL, Description text, GroupID int(11) NOT NULL default '0', PRIMARY KEY (CategoryID) ) TYPE=MyISAM; CREATE TABLE groups ( GroupID int(11) NOT NULL auto_increment, Name varchar(50) default NULL, DiscountID int(11) default NULL, PRIMARY KEY (GroupID) ) TYPE=MyISAM; Currently, there is a one-to-one relationship between listings and categories, and listings and groups. Now, the customer is requesting that a listing be included in several categories. I am not quite sure how to do this. My thought was to add a new field to the listings table that would contain a comma-separated list of CategoryIDs, but something doesn't feel right about this solution. What would be a good approach to this problem? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design Question
I think I understand. So instead of my queries being centered around the listings table, they will be centered around this new table? Currently, I pull the records for each category like so: $query = select l.CatalogNumber, l.PDFLink, l.PDFName, l.MP3Name, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, l.DiscountID, l.DiscountType, l.DiscountAmount, o.Alias, l.Description from listings l, publishers p, composers c, arrangers a, categories o where l.CategoryID=o.CategoryID and o.Name='.$Category.' and l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID order by .$OrderBy; To follow your example, I would add these other fields to the listings_projects table you defined below, and restructure the query around that? Thanks for your insight! -Erich- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 04, 2004 11:51 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Design Question As posted, your data structure supports two one-to-many relationships, not the one-to-one relationships as you described. You can have multiple Listings per Category and multiple Categories per Group. What it sounds like you have been asked to do is to support a many-to-many relationship. You need to support both multiple Listings per Category and multiple Categories per Listing. As you have it now: Groups (1..*) Categories (1..*) Listings As you need it to be: Groups (1..*) Categories (*..*) Listings To create a (*..*) relationship between two tables, you need a third table. Each entry in this table represents one Listing-Category association (relationship). CREATE TABLE listings_projects ( ListingID bigint not null , CategoryID int , ... any additional fields as needed ... , PRIMARY KEY (ListingID, CategoryID) ) The primary key ensures that at each Listing/Category combination appears only once (no duplicate assignments). I showed you where additional fields can fit into the relation table because sometimes there are facts about relationships that do not fit into either of the tables they relate A recent example in this list was a relation table between chemical compounds and the various plants in which those compounds could be found. A fact that belongs to the *relationship* could be the concentration of that chemical in that plant. That concentration value would not belong to the plants table nor would it belong to the compounds table but does belong to the relationship of plant to compound. Make sense? I have personally used additional fields like those to indicate deletion. That way old values are no longer available for new relationships (in my applications) and my queries won't break as I didn't actually get rid of any information. My historical reports still function as the old names are still in the system, even if you can't use the the old names for any current purposes. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Erich Beyrent [EMAIL PROTECTED] wrote on 08/04/2004 10:35:33 AM: Hi all, I need some advice on a project I have. Basically, I have some tables: CREATE TABLE listings ( ListingID bigint(20) unsigned NOT NULL auto_increment, CatalogNumber varchar(12) NOT NULL default '', PDFLink varchar(100) default NULL, PDFName varchar(80) default NULL, Title varchar(100) NOT NULL default '', ComposerID int(11) default NULL, ArrangerID int(11) default NULL, PublisherID int(11) default NULL, Price double(16,2) NOT NULL default '0.00', DiscountID int(11) default NULL, Description text, NewTitles tinyint(1) default NULL, CategoryID int(11) NOT NULL default '0', PRIMARY KEY (ListingID) ) TYPE=MyISAM; CREATE TABLE categories ( CategoryID int(11) NOT NULL auto_increment, Name varchar(50) NOT NULL default '', Alias varchar(60) default NULL, DiscountID int(11) default NULL, Description text, GroupID int(11) NOT NULL default '0', PRIMARY KEY (CategoryID) ) TYPE=MyISAM; CREATE TABLE groups ( GroupID int(11) NOT NULL auto_increment, Name varchar(50) default NULL, DiscountID int(11) default NULL, PRIMARY KEY (GroupID) ) TYPE=MyISAM; Currently, there is a one-to-one relationship between listings and categories, and listings and groups. Now, the customer is requesting that a listing be included in several categories. I am not quite sure how to do this. My thought was to add a new field to the listings table that would contain
RE: Design Question
I think you understand. Here is how I would re-write the query to use the new table: $query = SELECT l.CatalogNumber, l.PDFLink, l.PDFName, l.MP3Name, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, l.DiscountID, l.DiscountType, l.DiscountAmount, o.Alias, l.Description FROM listings l INNER JOIN publishers p ON l.PublisherID=p.PublisherID INNER JOIN composers c ON l.ComposerID=c.ComposerID INNER JOIN arrangers a ON l.ArrangerID=a.ArrangerID INNER JOIN listings_categories lc ON l.ListingID = lc.ListingID INNER JOIN categories o ON lc.CategoryID = o.CategoryID WHERE o.Name='.$Category.' ORDER BY .$OrderBy; (That's just the style I prefer as I can more easily spot which match-up conditions belong to which sets of tables. That way I am less likely to leave one out and accidentally create a cartesian product of any two tables. The comma separated style you use is absolutely, perfectly valid.) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Erich Beyrent [EMAIL PROTECTED] wrote on 08/04/2004 12:39:55 PM: I think I understand. So instead of my queries being centered around the listings table, they will be centered around this new table? Currently, I pull the records for each category like so: $query = select l.CatalogNumber, l.PDFLink, l.PDFName, l.MP3Name, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, l.DiscountID, l.DiscountType, l.DiscountAmount, o.Alias, l.Description from listings l, publishers p, composers c, arrangers a, categories o where l.CategoryID=o.CategoryID and o.Name='.$Category.' and l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID order by .$OrderBy; To follow your example, I would add these other fields to the listings_projects table you defined below, and restructure the query around that? Thanks for your insight! -Erich- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 04, 2004 11:51 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Design Question As posted, your data structure supports two one-to-many relationships, not the one-to-one relationships as you described. You can have multiple Listings per Category and multiple Categories per Group. What it sounds like you have been asked to do is to support a many-to-many relationship. You need to support both multiple Listings per Category and multiple Categories per Listing. As you have it now: Groups (1..*) Categories (1..*) Listings As you need it to be: Groups (1..*) Categories (*..*) Listings To create a (*..*) relationship between two tables, you need a third table. Each entry in this table represents one Listing-Category association (relationship). CREATE TABLE listings_projects ( ListingID bigint not null , CategoryID int , ... any additional fields as needed ... , PRIMARY KEY (ListingID, CategoryID) ) The primary key ensures that at each Listing/Category combination appears only once (no duplicate assignments). I showed you where additional fields can fit into the relation table because sometimes there are facts about relationships that do not fit into either of the tables they relate A recent example in this list was a relation table between chemical compounds and the various plants in which those compounds could be found. A fact that belongs to the *relationship* could be the concentration of that chemical in that plant. That concentration value would not belong to the plants table nor would it belong to the compounds table but does belong to the relationship of plant to compound. Make sense? I have personally used additional fields like those to indicate deletion. That way old values are no longer available for new relationships (in my applications) and my queries won't break as I didn't actually get rid of any information. My historical reports still function as the old names are still in the system, even if you can't use the the old names for any current purposes. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Erich Beyrent [EMAIL PROTECTED] wrote
Re: data design question
Justin French [EMAIL PROTECTED] wrote: Should I worry at 40,000? 100,000? Or will the indexing of the siteID keep everything extensible? Indexing is designed to keep SELECT speed small with no matter how much data it is. You should ensure that the siteID index is properly used (use EXPLAIN SELECT). Say, if half of the records belongs to a particular siteID - the index won't help, the full table scan will be done to find out the needed article. You may consider creating a complex index as well - say, (siteID, articleID). Then to fetch the list of all articles of a site the (siteID) index will be used, and to fetch particular article the compound index will be used. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
data design question
Hi all, I'm in the planning phase of a hosted web application where all instances of the app (a sort-of website CMS) will be running off a single code source. I've got a clear picture about everything except for the database design. a) I could have a separate database table structure for each website in the application b) I could have a single database for all instances, but individual table structures for each (eg client_tablename) c) I could have all data from all instances in one table structure, with a website_ID for each record, signifying which site the record relates to. I'm leaning towards (c) on the basis that updates to the database and table structure will be a breeze (only have to update one instance), but I'm concerned about performance. Let's say I had 20,000 articles belonging to 100 websites. Would there be a performance loss by having all 20,000 articles in one table, with a indexed `siteID` column identifying which site each article belongs to, as opposed to 100 tables (one for each site) holding only their own data?? Should I worry at 40,000? 100,000? Or will the indexing of the siteID keep everything extensible? --- Justin French http://indent.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table Relation Design Question
Hi all, I am having a little trouble deciding how to relate some of my tables together. I was wondering about creating one big lookup table to relate 3 or 4 tables together, but wasn't sure if that was a good id, or should I have a look up table For each pair of tables. Here is a simple example of my tables. Orgs: org_id org_name (org_id is primary key) Contacts: con_id, con_name, org_id (con_id is primary, org_id is foreign key) Events: ev_id, ev_name, org_id, con_id (ev_id is primary, con_id and org_id are foreign keys) This is centered around organizations, so every contact must belong to an org, likewise for an event. I will create an org called None in case they just want To track the occasional lone contact or internal event. But because an organization can have many contacts and many events, I was thinking of using lookup tables. I.e., Contacts are assigned to Organizations, So have a table called assigned with org_id and con_id as a composite primary key. And each is a foreign key back to the correct table... And should I have a table that links orgs and events and contacts and events, or should I have one lookup table That relates them all together, i.e. orgs contacts, and events..? To simplify, is It better to have many smaller lookup tables or one big one? Thanks, Taylor
Re: Table Relation Design Question
To simplify, is It better to have many smaller lookup tables or one big one? Traylor: You can create three entity tables (organization,contact,event) + the relation tables (org_contact, org_event, contact_event). Small lookup tables are usually better, and also give you points for sticking to a normalized design. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Relation Design Question
On Thu, 2004-05-20 at 07:13, Lewick, Taylor wrote: Hi all, I am having a little trouble deciding how to relate some of my tables together. I was wondering about creating one big lookup table to relate 3 or 4 tables together, but wasn't sure if that was a good id, or should I have a look up table For each pair of tables. Here is a simple example of my tables. Orgs: org_id org_name (org_id is primary key) Contacts: con_id, con_name, org_id (con_id is primary, org_id is foreign key) Events: ev_id, ev_name, org_id, con_id (ev_id is primary, con_id and org_id are foreign keys) Its probably unnecessary to have a 'con_id' in the Event table since you can get that information from the 'org_id'. An exception might be if multiple contacts for one organization are allowed, and for any given event you want the ability to specify just one of those contacts. Removing the con_id would also help eliminate the problem where an org_id on an event does not agree with the org_id of the contact given by con_id (input or programming gremlin). Or, you have an existing event where the contact has the same org_id as the event's org_id, but there is a change of contacts at org_id's organization. Someone updates the contact information and now your event either contains an invalid contact, or an ID to a non-existent contact This is centered around organizations, so every contact must belong to an org, likewise for an event. I will create an org called None in case they just want To track the occasional lone contact or internal event. But because an organization can have many contacts and many events, I was thinking of using lookup tables. I.e., Contacts are assigned to Organizations, So have a table called assigned with org_id and con_id as a composite primary key. And each is a foreign key back to the correct table... And should I have a table that links orgs and events and contacts and events, or should I have one lookup table That relates them all together, i.e. orgs contacts, and events..? To simplify, is It better to have many smaller lookup tables or one big one? Thanks, Taylor -- . Garth Webb . [EMAIL PROTECTED] . . shoes * * schoenen * * chaussures * zapatos . Schuhe * * pattini * * sapatas * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database design question
Hi, I'm writing a database for an online candle store. Here's the situation. This store sells all sorts of items including candles. Now the gist is that some of the candles can be made in different types of waxes and some only have one wax. My question is how do I resolve this when I write up the order and write up the line items. This almost seems like it is going to be some sort of a three way join or something.
Re: Database design question
JOHN MEYER [EMAIL PROTECTED] wrote on 07/04/2004 15:39:10: Hi, I'm writing a database for an online candle store. Here's the situation. This store sells all sorts of items including candles. Now the gist is that some of the candles can be made in different types of waxes and some only have one wax. My question is how do I resolve this when I write up the order and write up the line items. This almost seems like it is going to be some sort of a three way join or something. You need to provide more information. For example, do prices differ with different wax types? Are you expecting some sort of validation that candle type A can be manufactured in wax type B? The easiest answer is just to have an extra column waxtype and have the ordering application only allow valid settings to be put in it, allowing NULL in the case where there is no choice of waxes. You seem to have discarded this option - but you haven't told us why, so we cannot tell what alternative to suggest. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design question
Everything I've read about creating online stores is that you are selling inventory items, not the items that makeup the inventory item. So if you sell a red candle made from wax X, candle is the product and red wax X are two attributes of the product. Ideally your structure would work for any product, which means no columns like waxtype or color. Off the top of my head I'm thinking a product table with things like name, description, price, etc. An attribute table. And a productattribute table to link the two. Your attribute table could be a multipurpose table from which you could group your attributes into categories (i.e. wax type, colors, etc.). Something like this: IDPrime IDCategory AttrName AttrDesc The IDCategory field would be a relation to the IDPrime field (a self join). If the IDCategory field is '0', you know it's a top level category. This gives you the ability to create a hierarchy with unlimited depth. The data might look like this: 1 0 Candles Burns bright for hours 2 1 Shape Shapes of candles 3 2 Round 4 2 Square 5 2 Pyramid 6 1 Color Colors available 7 6 Red 8 6 Blue 9 6 Gold 10 1 Wax Type 11 10 X 12 10 Y 13 10 Z You can add as many attributes as you like without having to ever change your database structure. So if they are buying a Candle, you search the attributes for Candles and get Shape, Color and Wax type (scent, logo, etc). The user then picks each attribute. If you code it right, you wouldn't even have to change you code when you add attributes and categories. You then store the user selection in the productattribute table. The Product table would be pre-populated with the products you offer, including their links to the attributes. But the design is flexible enough to allow a user to create a custom product or customize and existing one. The shopping cart then only contains the link to the product table. Obviously there is a lot more too creating the whole system, but hopefully this gives you some ideas. On Apr 7, 2004, at 10:39 AM, JOHN MEYER wrote: Hi, I'm writing a database for an online candle store. Here's the situation. This store sells all sorts of items including candles. Now the gist is that some of the candles can be made in different types of waxes and some only have one wax. My question is how do I resolve this when I write up the order and write up the line items. This almost seems like it is going to be some sort of a three way join or something. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database design question
I'm not 100% sure as to what you are trying to do, however, the relationship you describe could, I believe, be modeled as: Candles (candleid, description, price) Waxes (waxid, name/description) Candle_Waxes (candleid, waxid) Thus one table holds the description and price of each candle, another table holds the name of each wax, and a third table connects the two - as a candle can have multiple waxes, the logical way to do this (to me, anyway) is via this third table - glueing the other two together. You'll need to be a bit clever when querying, as simplying joining all three together will bring back multiple rows for candles which contain more than one wax - this could be eliminated by not bringing back the wax details (and using distinct), or in a number of other ways. One other way might be to come up with a way to combine all of the wax names into one field (tricky - can't think how to do this in mysql, off the top of my head). What precisely are you trying to achieve, though - this might be completely wrong for you! Thanks, Matt -Original Message- From: JOHN MEYER [mailto:[EMAIL PROTECTED] Sent: 07 April 2004 15:39 To: [EMAIL PROTECTED] Subject: Database design question Hi, I'm writing a database for an online candle store. Here's the situation. This store sells all sorts of items including candles. Now the gist is that some of the candles can be made in different types of waxes and some only have one wax. My question is how do I resolve this when I write up the order and write up the line items. This almost seems like it is going to be some sort of a three way join or something. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multiple databases: design question
What are the advantages and disadvantages of using multiple databases, versus placing all tables in one uber-database? I understand and appreciate the organizational value of multiple databases, but what other issues are involved? I ask this because I'm considering moving from tables across multiple DBs (on one server) to all tables in one DB (on one server.) I don't want to do this, but may need to given the constraints of perl's Class::DBI and Ima::DBI. Specifically, I am stuck on successfully handling transactions and rollback across multiple databases, because Ima::DBI creates a different handle (eg connection) to each database, which stymies rollback (as the work is happening thru different cxns). I'd welcome any suggestions about structuring databases -- one vs. many -- and if anyone has advice about the perl issues, that'd be great too. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: multiple databases: design question
The not very useful answer would be: It depends on what you are trying to achieve. More usefully (I hope): I work with a complex web application which is also strongly modularized. This system is generally delivered using several databases, residing on different servers, to allow us to cope with more user activity. The whole set-up relies on quite complex replication, since some of the data in the system is required by all of the constituent parts. A large cluster, scaled to cope with thousands of users might theoretically consist of four database servers: Web, Master, Reporting, Processing. The first two are strongly paired, with the former serving all direct requests from web page users (e.g. viewing and submitting data). The second feeds 'centrally controlled' information (e.g. product information) to all of the other databases, and is the entry point for maintenance personnel who maintain the information within the system. The use of a reporting server is fairly standard - it allows us to shift the processing requirements of report generation onto a separate server, lowering the load on the web-facing one. Requests for reports are passed to this server which generates the required data and passes it to a separate system responsible for displaying/serving the finished report to the user. Finally the use of a 'processing' box, would theoretically allow any number crunching to be done away from the web-facing box - data requiring processing (e.g. an order which the user has submitted) is churned here, and the results are replicated back to other databases which require them. I don't know if it is technically possible to run cross-database transactions, but I would suspect not. However, I'm sure you can work around this without too much trouble - the use of replication so that all data 'originates' from one place and is controlled there can help to simplify this. To summarise, before I get carried away - if your application/system can be split down into logical sections, it may be advantageous both from the organizational point of view (as you say) and scalability pov too, to split it up into multiple databases. Thanks, Matt -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of TO Sent: 21 March 2004 15:14 To: [EMAIL PROTECTED] Subject: multiple databases: design question What are the advantages and disadvantages of using multiple databases, versus placing all tables in one uber-database? I understand and appreciate the organizational value of multiple databases, but what other issues are involved? I ask this because I'm considering moving from tables across multiple DBs (on one server) to all tables in one DB (on one server.) I don't want to do this, but may need to given the constraints of perl's Class::DBI and Ima::DBI. Specifically, I am stuck on successfully handling transactions and rollback across multiple databases, because Ima::DBI creates a different handle (eg connection) to each database, which stymies rollback (as the work is happening thru different cxns). I'd welcome any suggestions about structuring databases -- one vs. many -- and if anyone has advice about the perl issues, that'd be great too. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table design question
I have ip_address and ports that I want to use in my table. I was just going to make each one a varchar. But was wondering if anyone has a better suggestion? Should I use int for ports, which will have an index. Not sure how to store ip_address. This table has the possibility of having 800 millon records. Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table design question
rmck said: I have ip_address and ports that I want to use in my table. I was just going to make each one a varchar. But was wondering if anyone has a better suggestion? PostgreSQL ;-) It has a native datatype for storing IP addresses. That means that things like sorting and subnet inclusion tests come included with the database. But switching databases is not something to be undertaken lightly. Should I use int for ports, which will have an index. Not sure how to store ip_address. Is IPv6 an issue? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table design question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jochem van Dieten wrote: rmck said: I have ip_address and ports that I want to use in my table. I was just going to make each one a varchar. But was wondering if anyone has a better suggestion? PostgreSQL ;-) It has a native datatype for storing IP addresses. That means that things like sorting and subnet inclusion tests come included with the database. But switching databases is not something to be undertaken lightly. Or take a look at the INET_ATON() and INET_NTOA() functions in the MySQL manual at http://www.mysql.com/doc/en/Miscellaneous_functions.html that allow you to convert the IP address to an integer Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Want to swim with the dolphins? (April 14-16, 2004) http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAGS2MtvXNTca6JD8RAgkdAKC5rcoyIMJFOaklzT8TaLONQLPsqACfUImB p//aTmYu/i84jWOJ0PqX8Mk= =OODk -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table design question
Mark Matthews wrote: Jochem van Dieten wrote: rmck said: I have ip_address and ports that I want to use in my table. I was just going to make each one a varchar. But was wondering if anyone has a better suggestion? PostgreSQL ;-) It has a native datatype for storing IP addresses. That means that things like sorting and subnet inclusion tests come included with the database. But switching databases is not something to be undertaken lightly. Or take a look at the INET_ATON() and INET_NTOA() functions in the MySQL manual at http://www.mysql.com/doc/en/Miscellaneous_functions.html that allow you to convert the IP address to an integer Which is exactly why I asked if IPv6 was an issue. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table design question
rmck wrote: I have ip_address and ports that I want to use in my table. I was just going to make each one a varchar. But was wondering if anyone has a better suggestion? Should I use int for ports, which will have an index. Not sure how to store ip_address. Use a varchar for the IP address. It allows greater flexibility and will adapt to format changes much more easily. An int for the port number would work well. -- jimoe at sohnen-moe dot com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: Design question
I have a contract that has about 6 fields that need to be filled out each time it is signed. My goal is to have a form that only asks for the missing information then includes that into the contract which is then printed and then stored in a database completed. There would be 3 unique things about each contract. The contractor's name, the contract date, and the Route#. Can anyone give some quidance as to where I would look for guidance on how to design this? I know almost nothing about SQL. I don't mind if its either a web form ( ideal ) or openoffice document. Troy
RE: DB design question - shell scripting...
That worked like a charm, thanks so much! I don't know why I didn't try that before! Julian At 02:46 PM 11/21/2003 -0600, Paul DuBois wrote: At 10:56 -0500 11/21/03, Julian Zottl wrote: Andy, Thanks for responding. I think that I am going to go with the idea of creating a tale for each day. My thoughts were to write a shell script to do this for me, but I am running into a problem: I wrote the following: #!/bin/sh date=`date +%m%d%Y` export date mysql -u root -p createdb.sql Then in createdb.sql CONNECT Blah; CREATE TABLE $date ( . ) TYPE=MyISAM; But it's not passing the $date variable to SQL :/ I've been looking on the web for a way to do this, but have yet to find it. any ideas? You could use a here-document instead of createdb.sql #!/bin/sh date=`date +%m%d%Y` mysql -u root -p EOF CONNECT Blah; CREATE TABLE t$date ( .. ) TYPE=MyISAM; EOF I put a t before $date -- you don't want to try creating a table with a name that's all digits. That makes it indistinguishable from a number, so you'd have to quote it with backticks every time you refer to it. Julian -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Julian Zottl Unix Systems Administrator NASA HQ - 202-358-1682 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB design question - shell scripting...
Wouldn't this also work?: mysql -u root -p -e CREATE TABLE t$date(...) yourdatabase -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Friday, November 21, 2003 12:46 PM To: Julian Zottl; Andy Eastham; Mysql List Subject: RE: DB design question - shell scripting... At 10:56 -0500 11/21/03, Julian Zottl wrote: Andy, Thanks for responding. I think that I am going to go with the idea of creating a tale for each day. My thoughts were to write a shell script to do this for me, but I am running into a problem: I wrote the following: #!/bin/sh date=`date +%m%d%Y` export date mysql -u root -p createdb.sql Then in createdb.sql CONNECT Blah; CREATE TABLE $date ( . ) TYPE=MyISAM; But it's not passing the $date variable to SQL :/ I've been looking on the web for a way to do this, but have yet to find it. any ideas? You could use a here-document instead of createdb.sql #!/bin/sh date=`date +%m%d%Y` mysql -u root -p EOF CONNECT Blah; CREATE TABLE t$date ( . ) TYPE=MyISAM; EOF I put a t before $date -- you don't want to try creating a table with a name that's all digits. That makes it indistinguishable from a number, so you'd have to quote it with backticks every time you refer to it. Julian -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB design question - shell scripting...
At 23:34 -0800 11/21/03, Chris wrote: Wouldn't this also work?: mysql -u root -p -e CREATE TABLE t$date(...) yourdatabase Sure. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Friday, November 21, 2003 12:46 PM To: Julian Zottl; Andy Eastham; Mysql List Subject: RE: DB design question - shell scripting... At 10:56 -0500 11/21/03, Julian Zottl wrote: Andy, Thanks for responding. I think that I am going to go with the idea of creating a tale for each day. My thoughts were to write a shell script to do this for me, but I am running into a problem: I wrote the following: #!/bin/sh date=`date +%m%d%Y` export date mysql -u root -p createdb.sql Then in createdb.sql CONNECT Blah; CREATE TABLE $date ( . ) TYPE=MyISAM; But it's not passing the $date variable to SQL :/ I've been looking on the web for a way to do this, but have yet to find it. any ideas? You could use a here-document instead of createdb.sql #!/bin/sh date=`date +%m%d%Y` mysql -u root -p EOF CONNECT Blah; CREATE TABLE t$date ( . ) TYPE=MyISAM; EOF I put a t before $date -- you don't want to try creating a table with a name that's all digits. That makes it indistinguishable from a number, so you'd have to quote it with backticks every time you refer to it. Julian -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DB design question
Hello all, I am designing a database right now that will have between 300-400k inserts per day. I need to keep this information for approximately 3 months and will probably do 5-10 reads on the data set per day. I've been storing it in one table up to now (only col.), but the searches are becoming more and more of a problem. I'd like to break it up so that I have one table for every day, and then I'll just delete the trailing days when I create a new day. So I would have 90 tables of roughly 350k records instead of a single table with 6+ million records. What do you al think of this design? I'm making an assumption that it will make my searches a lot faster for a single day (I doubt I would ever need to search on more than one day). Thanks! Julian Zottl Unix Systems Administrator NASA HQ - 202-358-1682 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB design question
Julian, Your design is sound in my opinion. An area you probably need to consider is when you need to search across a day boundary. You will need to make the application aware that it needs to search across a day boundary, so that it searches two tables with a union where necessary. It will also need to know what the oldest table is, so that it doesn't try to do a union with a table that doesn't exist. Alternatively, you could always search across three tables - so that you always union with the one before and one after the required time window. Of course, you again need to check that you're not searching the earliest or latest available table, and if so, modify the union so that you don't try to search a non-existent table. Hope this helps, Andy -Original Message- From: Julian Zottl [mailto:[EMAIL PROTECTED] Sent: 21 November 2003 12:03 To: [EMAIL PROTECTED] Subject: DB design question Hello all, I am designing a database right now that will have between 300-400k inserts per day. I need to keep this information for approximately 3 months and will probably do 5-10 reads on the data set per day. I've been storing it in one table up to now (only col.), but the searches are becoming more and more of a problem. I'd like to break it up so that I have one table for every day, and then I'll just delete the trailing days when I create a new day. So I would have 90 tables of roughly 350k records instead of a single table with 6+ million records. What do you al think of this design? I'm making an assumption that it will make my searches a lot faster for a single day (I doubt I would ever need to search on more than one day). Thanks! Julian Zottl Unix Systems Administrator NASA HQ - 202-358-1682 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB design question - shell scripting...
Andy, Thanks for responding. I think that I am going to go with the idea of creating a tale for each day. My thoughts were to write a shell script to do this for me, but I am running into a problem: I wrote the following: #!/bin/sh date=`date +%m%d%Y` export date mysql -u root -p createdb.sql Then in createdb.sql CONNECT Blah; CREATE TABLE $date ( . ) TYPE=MyISAM; But it's not passing the $date variable to SQL :/ I've been looking on the web for a way to do this, but have yet to find it. any ideas? Julian At 12:37 PM 11/21/2003 +, Andy Eastham wrote: Julian, Your design is sound in my opinion. An area you probably need to consider is when you need to search across a day boundary. You will need to make the application aware that it needs to search across a day boundary, so that it searches two tables with a union where necessary. It will also need to know what the oldest table is, so that it doesn't try to do a union with a table that doesn't exist. Alternatively, you could always search across three tables - so that you always union with the one before and one after the required time window. Of course, you again need to check that you're not searching the earliest or latest available table, and if so, modify the union so that you don't try to search a non-existent table. Hope this helps, Andy -Original Message- From: Julian Zottl [mailto:[EMAIL PROTECTED] Sent: 21 November 2003 12:03 To: [EMAIL PROTECTED] Subject: DB design question Hello all, I am designing a database right now that will have between 300-400k inserts per day. I need to keep this information for approximately 3 months and will probably do 5-10 reads on the data set per day. I've been storing it in one table up to now (only col.), but the searches are becoming more and more of a problem. I'd like to break it up so that I have one table for every day, and then I'll just delete the trailing days when I create a new day. So I would have 90 tables of roughly 350k records instead of a single table with 6+ million records. What do you al think of this design? I'm making an assumption that it will make my searches a lot faster for a single day (I doubt I would ever need to search on more than one day). Thanks! Julian Zottl Unix Systems Administrator NASA HQ - 202-358-1682 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Julian Zottl Unix Systems Administrator NASA HQ - 202-358-1682 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB design question
I was wondering if maybe your SELECTs could be optimized? Are you using an index? MySQL can handle billions of rows w/o a problem. -will Real-time Chat: irc.freenode.net - #mysql ( http://www.mysql.com/doc/en/IRC.html ) - Original Message - From: Julian Zottl [EMAIL PROTECTED] To: Andy Eastham [EMAIL PROTECTED]; Mysql List [EMAIL PROTECTED] Sent: Friday, November 21, 2003 7:56 AM Subject: RE: DB design question - shell scripting... Andy, Thanks for responding. I think that I am going to go with the idea of creating a tale for each day. My thoughts were to write a shell script to do this for me, but I am running into a problem: I wrote the following: #!/bin/sh date=`date +%m%d%Y` export date mysql -u root -p createdb.sql Then in createdb.sql CONNECT Blah; CREATE TABLE $date ( . ) TYPE=MyISAM; But it's not passing the $date variable to SQL :/ I've been looking on the web for a way to do this, but have yet to find it. any ideas? Julian At 12:37 PM 11/21/2003 +, Andy Eastham wrote: Julian, Your design is sound in my opinion. An area you probably need to consider is when you need to search across a day boundary. You will need to make the application aware that it needs to search across a day boundary, so that it searches two tables with a union where necessary. It will also need to know what the oldest table is, so that it doesn't try to do a union with a table that doesn't exist. Alternatively, you could always search across three tables - so that you always union with the one before and one after the required time window. Of course, you again need to check that you're not searching the earliest or latest available table, and if so, modify the union so that you don't try to search a non-existent table. Hope this helps, Andy -Original Message- From: Julian Zottl [mailto:[EMAIL PROTECTED] Sent: 21 November 2003 12:03 To: [EMAIL PROTECTED] Subject: DB design question Hello all, I am designing a database right now that will have between 300-400k inserts per day. I need to keep this information for approximately 3 months and will probably do 5-10 reads on the data set per day. I've been storing it in one table up to now (only col.), but the searches are becoming more and more of a problem. I'd like to break it up so that I have one table for every day, and then I'll just delete the trailing days when I create a new day. So I would have 90 tables of roughly 350k records instead of a single table with 6+ million records. What do you al think of this design? I'm making an assumption that it will make my searches a lot faster for a single day (I doubt I would ever need to search on more than one day). Thanks! Julian Zottl Unix Systems Administrator NASA HQ - 202-358-1682 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Julian Zottl Unix Systems Administrator NASA HQ - 202-358-1682 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB design question - shell scripting...
At 10:56 -0500 11/21/03, Julian Zottl wrote: Andy, Thanks for responding. I think that I am going to go with the idea of creating a tale for each day. My thoughts were to write a shell script to do this for me, but I am running into a problem: I wrote the following: #!/bin/sh date=`date +%m%d%Y` export date mysql -u root -p createdb.sql Then in createdb.sql CONNECT Blah; CREATE TABLE $date ( . ) TYPE=MyISAM; But it's not passing the $date variable to SQL :/ I've been looking on the web for a way to do this, but have yet to find it. any ideas? You could use a here-document instead of createdb.sql #!/bin/sh date=`date +%m%d%Y` mysql -u root -p EOF CONNECT Blah; CREATE TABLE t$date ( . ) TYPE=MyISAM; EOF I put a t before $date -- you don't want to try creating a table with a name that's all digits. That makes it indistinguishable from a number, so you'd have to quote it with backticks every time you refer to it. Julian -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Basic Database Design Question
Thanks guys. I wasn't very clear in my original email. I already had a business section table (with a primary key). My problem was, I couldn't figure out how to accurately store (for information retrieval purposes) information about business sections that use particular applications. I was orignally thinking about putting something like FMB/HR/OM, for example, as one character string in the 'used_by' column of the apps table. I now know that using multiple values as one string is NOT the way to go! The answer, as Jerimiah pointed out, if an INTERSECTION TABLE!! app_id, branch_id in one table, with app_branch_id being the primary key! Each row in this table will represent an app-branch use relationship scenario! Once I got this, it was like a huge lightbulb going off in my head! Wow! I have setup my intersection table and it works great. My intersection table, luckily, only has 21 rows. My next question is this: How in the world do you setup an intersection table (when designing the database) the use primary keys with thousands of rows? I could imagine an intersection table having thousands of scenario combinations. How do you tackle something like that? Thanks again guys! --- Jeremiah Jacks [EMAIL PROTECTED] wrote: You could do it many different ways. Here's one that I would consider: Create a table to store the business sections (buss_section) and a table to relate the apps with the business sections (app_buss_section). The design would abstract the app info from its usage and create a manageable table of business sections which could be easily modified. Something like this should work... Table nms_apps (..., id) Table buss_section (..., id) Table app_buss_section (nms_apps.id, buss_section.id) -Jeremiah -Original Message- From: James Walters [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 9:42 AM To: [EMAIL PROTECTED] Subject: Basic Database Design Question Hello, DB novice checking in here with a basic design question. I have a table called 'nms_apps' which stores information about all of our applications which we have developed/maintained for our client. One column which I would like to use is called 'used_by', which would store information about which business sections (Financial Management Branch, Human Resources Branch, etc.) use a particular application. Often times more than one section use a particular application. My question is this: How do you handle multiple entries in one column? Is it acceptable to more than one value in a column for one row? If not, what is the best way to design that in the table? Have multiple 'used_by' columns? 'used_by_1', 'used_by_2', 'used_by_3', etc.? Thanks in advance, Testudo __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Basic Database Design Question
Hello, DB novice checking in here with a basic design question. I have a table called 'nms_apps' which stores information about all of our applications which we have developed/maintained for our client. One column which I would like to use is called 'used_by', which would store information about which business sections (Financial Management Branch, Human Resources Branch, etc.) use a particular application. Often times more than one section use a particular application. My question is this: How do you handle multiple entries in one column? Is it acceptable to more than one value in a column for one row? If not, what is the best way to design that in the table? Have multiple 'used_by' columns? 'used_by_1', 'used_by_2', 'used_by_3', etc.? Thanks in advance, Testudo __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Basic Database Design Question
This is, as you guess, a very basic issue for relational databases. You never want to put multiple entries in one column, as you describe it. That's the scenario in which you create another table and use a key to join the two. For your project, you should have a column in the nms_apps table that uniquely identifies it -- the primary key, usually. You'll want to create a table, perhaps called 'user,' containing the 'used_by' information. That table would have, at a minimum, a column for the nms_apps key and a column that contains the user information. Then to find out who uses a given application, the query would be along these lines (this uses the column 'app_id' as the key: SELECT used_by FROM nms_apps, user WHERE nms_apps.app_id = user.app_id AND app_name = Application Foo Hope that helps. If you grasp this, you'll have the basic idea of relational data. Nick -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -Original Message- From: James Walters [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 10:42 AM To: [EMAIL PROTECTED] Subject: Basic Database Design Question Hello, DB novice checking in here with a basic design question. I have a table called 'nms_apps' which stores information about all of our applications which we have developed/maintained for our client. One column which I would like to use is called 'used_by', which would store information about which business sections (Financial Management Branch, Human Resources Branch, etc.) use a particular application. Often times more than one section use a particular application. My question is this: How do you handle multiple entries in one column? Is it acceptable to more than one value in a column for one row? If not, what is the best way to design that in the table? Have multiple 'used_by' columns? 'used_by_1', 'used_by_2', 'used_by_3', etc.? Thanks in advance, Testudo __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Basic Database Design Question
You could do it many different ways. Here's one that I would consider: Create a table to store the business sections (buss_section) and a table to relate the apps with the business sections (app_buss_section). The design would abstract the app info from its usage and create a manageable table of business sections which could be easily modified. Something like this should work... Table nms_apps (..., id) Table buss_section (..., id) Table app_buss_section (nms_apps.id, buss_section.id) -Jeremiah -Original Message- From: James Walters [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 9:42 AM To: [EMAIL PROTECTED] Subject: Basic Database Design Question Hello, DB novice checking in here with a basic design question. I have a table called 'nms_apps' which stores information about all of our applications which we have developed/maintained for our client. One column which I would like to use is called 'used_by', which would store information about which business sections (Financial Management Branch, Human Resources Branch, etc.) use a particular application. Often times more than one section use a particular application. My question is this: How do you handle multiple entries in one column? Is it acceptable to more than one value in a column for one row? If not, what is the best way to design that in the table? Have multiple 'used_by' columns? 'used_by_1', 'used_by_2', 'used_by_3', etc.? Thanks in advance, Testudo __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
design question
This question came up at work today, would like to get some fellow developer's opinions: Scenario 1: 2 tables to track checks in terms of HD space a separate table would have been smaller since the checks table grows at 1000+ per week, but there would only be a handful of returned checks. In terms of cpu overhead, 90% of ops would have gotten a checkid from the checks table and then a search on the actions table indexed on checkid. Since there would be few actions per checks it shouldn't have been too bad. CREATE TABLE returned_checks ( returned_checksid int(11) NOT NULL auto_increment, webm_entityid int(11) NOT NULL default '0', checkno int(11) NOT NULL default '0', processed datetime NOT NULL default '-00-00 00:00:00', mirr_isdeleted tinyint(3) unsigned NOT NULL default '0', timestamp timestamp(14) NOT NULL, KEY wid (webm_entityid), KEY rid (returned_checksid) ) CREATE TABLE newpayday_checks ( type varchar(16) NOT NULL default '', amount float(9,2) unsigned NOT NULL default '0.00', textamount varchar(128) NOT NULL default '', name varchar(64) NOT NULL default '', webm_entityid int(11) NOT NULL default '0', addr1 varchar(60) NOT NULL default '', addr2 varchar(60) NOT NULL default '', citystatezip varchar(70) NOT NULL default '', country varchar(40) NOT NULL default '', socialsec varchar(15) NOT NULL default '', checkno int(11) NOT NULL default '0', date date NOT NULL default '-00-00', sent datetime NOT NULL default '-00-00 00:00:00', mirr_isdeleted tinyint(1) unsigned NOT NULL default '0', timestamp timestamp(14) NOT NULL, paydayfile datetime NOT NULL default '-00-00 00:00:00', stopped datetime NOT NULL default '-00-00 00:00:00', originalcheckid int(11) NOT NULL default '0', checkid int(11) NOT NULL auto_increment, checkdate date NOT NULL default '-00-00', KEY name (name,date), KEY checkid (checkid), KEY checkno (checkno), KEY mirr_isdeleted (mirr_isdeleted), KEY socialsec (socialsec), KEY webm_entityid (webm_entityid) ) TYPE=MyISAM; Scenario 2: one table to track checks (with a returned date in it) Reasons behind this are: * less duplication of data and indexes therefore less disk space which in turn means less disk IO * less chance of becoming out-of-sync, more data integrity * less joins (since we'll likely be joining this table) means less cpu overhead * less management, one table to maintain * one less entry in the mysql table cache (only holds so many) CREATE TABLE newpayday_checks ( type varchar(16) NOT NULL default '', amount float(9,2) unsigned NOT NULL default '0.00', textamount varchar(128) NOT NULL default '', name varchar(64) NOT NULL default '', webm_entityid int(11) NOT NULL default '0', addr1 varchar(60) NOT NULL default '', addr2 varchar(60) NOT NULL default '', citystatezip varchar(70) NOT NULL default '', country varchar(40) NOT NULL default '', socialsec varchar(15) NOT NULL default '', checkno int(11) NOT NULL default '0', date date NOT NULL default '-00-00', sent datetime NOT NULL default '-00-00 00:00:00', mirr_isdeleted tinyint(1) unsigned NOT NULL default '0', timestamp timestamp(14) NOT NULL, paydayfile datetime NOT NULL default '-00-00 00:00:00', stopped datetime NOT NULL default '-00-00 00:00:00', originalcheckid int(11) NOT NULL default '0', checkid int(11) NOT NULL auto_increment, checkdate date NOT NULL default '-00-00', returned datetime NOT NULL default '-00-00 00:00:00', KEY name (name,date), KEY checkid (checkid), KEY checkno (checkno), KEY mirr_isdeleted (mirr_isdeleted), KEY socialsec (socialsec), KEY webm_entityid (webm_entityid) ) TYPE=MyISAM; Which Scenario in your opinion, is better and why? -Ryan.
Database Design Question...
Hello... I am currently working on a User Account Management system. I am actually a Unix SA who is moonlighting at work as a MySQL DBA and web developer. I am learning a lot and enjoying the work, but I am severely lacking in database design fundamentals. I have created a couple very simple databases, but my two newest projects are getting more sophisticated. I was hoping for some DB design help with the following example... Let's say that I have users Mary, Joe, Frank, and Dan. I also have servers panther, cheetah, jaguar and lion. The data for each account that I want to maintain is UID, GID, home directory, and default shell. In designing a table or tables to handle this example what can I make as a primary key? My idea was to have a table named mary, with a row for each server, and each column would hold the data such as UID, GID, etc. This would mean that the primary key for each row would simply be the server name. By holding all of the data, including server name, in a single table, I am not sure how I would define a primary key. I couldn't use the user name or server name as there would be duplication. I suppose I could use a dummy numeric field that is auto-incrementing, but I am not sure how good an idea this is. I think I have read somewhere that you can actually use a combination of multiple columns as a primary key or index, but this is something I am obviously not familiar with. One other concern I have is regarding performance. The database work I have done so far has been dealing with relatively miniscule amounts of data. This database table however is going to contain information for about 80 servers with somewhere around 300 users per server on average. This is quite a large number of rows from my very limited experience. I don't want to come up with a poor table design that ends up causing problems down the line. Well, that's about all I can think of at the moment. I am sure that I will have plenty more questions as this progresses. Thanks again for the feedback. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Design Question...
[snip] Let's say that I have users Mary, Joe, Frank, and Dan. I also have servers panther, cheetah, jaguar and lion. The data for each account that I want to maintain is UID, GID, home directory, and default shell. In designing a table or tables to handle this example what can I make as a primary key? My idea was to have a table named mary, with a row With the name mary, are you implying a separate table for each user? If so, that doesn't sound right. I think you should have three tables: (1) A table of users, with properties that depend on the user but not those that might vary with server; (2) A table of servers, with properties that are indepedent of users; (3) A table with primary key formed by a pair of foreign keys, one pointing at the user table, one at the server table. This table would have properties that depend on the user/server combination. (An example would be the shell, since presumably a user could have different shells on different servers.) for each server, and each column would hold the data such as UID, GID, etc. This would mean that the primary key for each row would simply be the server name. By holding all of the data, including server name, in a single table, I am not sure how I would define a primary key. I couldn't use the user name or server name as there would be duplication. I suppose I could use a dummy numeric field that is auto-incrementing, but I am not sure how good an idea this is. I think I have read somewhere that you can actually use a combination of multiple columns as a primary key or index, but this is something I am obviously not familiar with. One other concern I have is regarding performance. The database work I have done so far has been dealing with relatively miniscule amounts of data. This database table however is going to contain information for about 80 servers with somewhere around 300 users per server on average. This is quite a large number of rows from my very limited experience. I Nah, doesn't sound like much data to me. don't want to come up with a poor table design that ends up causing problems down the line. [snip] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Design Question...
On 18-Jun-2003 NIPP, SCOTT V (SBCSI) wrote: Hello... I am currently working on a User Account Management system. I am actually a Unix SA who is moonlighting at work as a MySQL DBA and web developer. I am learning a lot and enjoying the work, but I am severely lacking in database design fundamentals. I have created a couple very simple databases, but my two newest projects are getting more sophisticated. I was hoping for some DB design help with the following example... Let's say that I have users Mary, Joe, Frank, and Dan. I also have servers panther, cheetah, jaguar and lion. The data for each account that I want to maintain is UID, GID, home directory, and default shell. In designing a table or tables to handle this example what can I make as a primary key? My idea was to have a table named mary, with a row for each server, and each column would hold the data such as UID, GID, etc. This would mean that the primary key for each row would simply be the server name. By holding all of the data, including server name, in a single table, I am not sure how I would define a primary key. I couldn't use the user name or server name as there would be duplication. I suppose I could use a dummy numeric field that is auto-incrementing, but I am not sure how good an idea this is. I think I have read somewhere that you can actually use a combination of multiple columns as a primary key or index, but this is something I am obviously not familiar with. One other concern I have is regarding performance. The database work I have done so far has been dealing with relatively miniscule amounts of data. This database table however is going to contain information for about 80 servers with somewhere around 300 users per server on average. This is quite a large number of rows from my very limited experience. I don't want to come up with a poor table design that ends up causing problems down the line. Well, that's about all I can think of at the moment. I am sure that I will have plenty more questions as this progresses. Thanks again for the feedback. 300 * 80 =24,000 rows --this is _NOT_ a lot. At first, I'd use just one table: srvr varchar(32) NOT NULL, login varchar(32) NOT NULL, uid smallint unsigned NOT NULL DEFAULT 1000, gid smallint unsigned NOT NULL DEFAULT 1000, gecos varchar(128), sh varchar(32) NOT NULL DEFAULT '/bin/sh', home varchar(64), ... more fields ... primary key (login,srvr)) A next refinement would change the 'srvr' field to: srvr tinyint unsigned and create a 'server' table: id tinyint unsigned AUTO_INCREMENT, // last octet of ip ? name varchar(64) NOT NULL, primary key (id)) That'll make it easy to rename servers. The table(s) could be populated very easily with a couple of shell, Perl, and/or awk scripts. YP/NIS would come in handy as well. You'd loop on each distinct server name --request a ypxfer of the passwd map, then suck it into your table. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql design question ?
| user_id | Marks | Now I have an other table where I keep the name etc.. from each user. You will all kill me for this, but in access you could the setup a relation between user_id and the id of the user in the user table. Is something like that possible in mysql ? So that if I select some data from the | user_id | Marks | table I get his name instead of his ID ? or should I first get his ID, and with that ID query his marks out of the table ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql design question ?
What you need to do is specify username in your select query and JOIN the two tables together. Marks table: User_id | Marks User Table User_id | Name Query: SELECT User.Name, Marks.Marks FROM User, Marks WHERE User.User_id = Marks.User_id Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Jonas Geiregat [mailto:[EMAIL PROTECTED] Sent: Monday, June 16, 2003 9:55 AM To: [EMAIL PROTECTED] Subject: mysql design question ? | user_id | Marks | Now I have an other table where I keep the name etc.. from each user. You will all kill me for this, but in access you could the setup a relation between user_id and the id of the user in the user table. Is something like that possible in mysql ? So that if I select some data from the | user_id | Marks | table I get his name instead of his ID ? or should I first get his ID, and with that ID query his marks out of the table ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie design question
Dear all, I'm designing a database for a travel company but i'm puzzled.. Maybe you can shed a light on this: I've got an travelpackage that can exicts of more possible departure date's but it has also have more than one accommodation. So 3 tables: (table one) -Travelpackageid (Primary Key) -Travelpackname etc (table two) -dateid (Primary Key) -date (table three) -accommodationid (Primary Key) -accommodationname etc Must a create 2 Join tables (travelpackageid/dateid and travelpackageid/accommodationid)??? Please help, Frank
Database design question
I am fairly new to this, so please bare with me on this. I am designing a database that stores information about the computers I manage, and am developing a PHP front end to add, view, and edit information about the computers. One issue I ran into is designing the hard drive information. Some computers have one hard drive, some have 2, 4, 16, etc. I am wanting to learn how to deal with this the correct way, so I want to create a separate table that stores the information about the drives, and have a foreign key that specifies the computer that owns the drive. I posted a message on the PHP-DB list, and received an answer that seems confusing. It told me that I needed InnoDB to be able to deal with this issue, but in the MySql documentation is says this:InnoDB provides MySQL with a transaction-safe (ACID compliant) table handler with commit, rollback, and crash recovery capabilities. Now having transactions available would be nice, but that is not my problem now. Now in another explaination on how to deal with relationships in MySql and with PHP it shows that you use PHP to deal with the relationships. I always thought that you let the database deal with the relationships, and not the application you are writing. Which is correct? I would think that if I created a relationship between tables, and I called for information on the computer, I would automatically get all of the drive information. Am I correct in this? Here is an example of the tables I have: ComputerTable computer_id - primary key computer_name etc. HardDriveTable drive_id - primary key computer_id - foreign key drive-capacity etc. If it is better to use the application code, I can do that, but I want to do things the right way, not necessarily the easiest. Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
A design question
Hi, I have a design question for mysql database that I am wanting to create a my movies collection. I was going to do as one-to-one database, but I see now that I need to do as a one-to-many. I have never done a one-to-many but I see that is the way to go. I guess then is can someone show me where I can see one-to-many example, and how you do sql statements? To make it clear, I know that I have to do table for the movie, basic information. Then a table for actor/actress. I know I need a field in both tables that are the same so they are related. I am sorry if I don't make sense, but my head is spinning with trying to understand on one-to-many works. Chuck Payne Magi Design and Support - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
A DB Design Question
I would like to set up a MySql database that will store distance information for a milage lookup program. The user will enter the origin city and the destination city for some predefined trips. Then the query will need to return the milage broken down by state. For example, for an origin city in NJ and a destination city in OH, the program would need to return how many miles were traveled within NJ, PA, and OH. It would be very inefficient to use one large table with columns for each state in the USA because most of them would be empty. A two table approach would use a primary table to store the origin, destination, and a unique reference key. While the secondary table would hold the reference key as a foreign key and a record for each state name and mileage. If anyone has had a similar design challenge I would appreciate hearing any comments you might have. Thanks -- Michael __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
