Re: [PHP-DB] Designing a database
I want to thank *everyone* that sent me a reply on this matter. Trouble is you guys' messages were so good that I have to study the suckers first before I know if I need to ask for further clarification. So I might be back in a couple of days ;) Thanks a bunch, guys! Later -- -duke Calgary, Alberta, Canada -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Designing a database
Good Point, Richard - sensible normalization - if duplicating a value here and there saves you from having to reference 12 tables instead of 3, yes, by all means. Also, adapting some standards as to using meaningfull names for columns - if fieldWidgetSize is char(25) - don't call it something else if it appears elsewhere - and don't change it's type! Phil J. - Original Message - From: "Richard Scott Crawford" [EMAIL PROTECTED] To: "Phil Jackson" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, April 22, 2001 1:17 AM Subject: Re: [PHP-DB] Designing a database Don't, however, go overboard with trying to normalize your database. Don't get me wrong: normalization is good because it saves disk and memory space (and is quite elegant as well); however, too much normalization can come at a price in PHP in terms of application speed and server overhead (not to mention creating coding nightmares if you're using your web-based application to enter data into your database as well as pull information from it). Original Message On 4/21/01, 10:10:19 PM, Phil Jackson [EMAIL PROTECTED] wrote regarding Re: [PHP-DB] Designing a database: Well to start with, I would examine the nature of the data you want to store, it's datatype, size, ect. Follow the other reccommendations as to not repeating data across tables. I tend to use autonumber fields for keys quite often. Also, think about what data could go into "domain" tables where you have a fairly non-volitile and finite set of values, and you plan on searching on this, and don't want to risk the vagaries of a user spelling it a dozen different ways when entereing the datafor example - if you have a fixed number of locations, or computer make - i.e.Dell, Compaq, etc. Also, be sure and provide admin pages to update these domain tables...maybe next month your company starts buying ABC-brand pcs - add it to the domain table. When entereing or searching - the values in these domain tables populate drop-down listboxes - not left to chance to be entered in a text field Phil J. Joni Jrvinen - Wandu wrote: Hey I'm quite new to databases so I though I'd ask you for some tips in designing a database. The db that I'm supposed to design holds information about workstations: Motherboard (Motherboard id, # of pci slots, agp slot etc etc.), Harddisks (Size, in what ide and master/slave, etc), the physical location of the workstation and it's hardware configuration etc. So if you could give me some tips and pointers for what tables to create etc I would be grateful. TIA -Joni- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Designing a database
Well to start with, I would examine the nature of the data you want to store, it's datatype, size, ect. Follow the other reccommendations as to not repeating data across tables. I tend to use autonumber fields for keys quite often. Also, think about what data could go into "domain" tables where you have a fairly non-volitile and finite set of values, and you plan on searching on this, and don't want to risk the vagaries of a user spelling it a dozen different ways when entereing the datafor example - if you have a fixed number of locations, or computer make - i.e.Dell, Compaq, etc. Also, be sure and provide admin pages to update these domain tables...maybe next month your company starts buying ABC-brand pcs - add it to the domain table. When entereing or searching - the values in these domain tables populate drop-down listboxes - not left to chance to be entered in a text field Phil J. Joni Jrvinen - Wandu wrote: Hey I'm quite new to databases so I though I'd ask you for some tips in designing a database. The db that I'm supposed to design holds information about workstations: Motherboard (Motherboard id, # of pci slots, agp slot etc etc.), Harddisks (Size, in what ide and master/slave, etc), the physical location of the workstation and it's hardware configuration etc. So if you could give me some tips and pointers for what tables to create etc I would be grateful. TIA -Joni- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Designing a database
Don't, however, go overboard with trying to normalize your database. Don't get me wrong: normalization is good because it saves disk and memory space (and is quite elegant as well); however, too much normalization can come at a price in PHP in terms of application speed and server overhead (not to mention creating coding nightmares if you're using your web-based application to enter data into your database as well as pull information from it). Original Message On 4/21/01, 10:10:19 PM, Phil Jackson [EMAIL PROTECTED] wrote regarding Re: [PHP-DB] Designing a database: Well to start with, I would examine the nature of the data you want to store, it's datatype, size, ect. Follow the other reccommendations as to not repeating data across tables. I tend to use autonumber fields for keys quite often. Also, think about what data could go into "domain" tables where you have a fairly non-volitile and finite set of values, and you plan on searching on this, and don't want to risk the vagaries of a user spelling it a dozen different ways when entereing the datafor example - if you have a fixed number of locations, or computer make - i.e.Dell, Compaq, etc. Also, be sure and provide admin pages to update these domain tables...maybe next month your company starts buying ABC-brand pcs - add it to the domain table. When entereing or searching - the values in these domain tables populate drop-down listboxes - not left to chance to be entered in a text field Phil J. Joni Jrvinen - Wandu wrote: Hey I'm quite new to databases so I though I'd ask you for some tips in designing a database. The db that I'm supposed to design holds information about workstations: Motherboard (Motherboard id, # of pci slots, agp slot etc etc.), Harddisks (Size, in what ide and master/slave, etc), the physical location of the workstation and it's hardware configuration etc. So if you could give me some tips and pointers for what tables to create etc I would be grateful. TIA -Joni- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Designing a database
On Sun, Apr 22, 2001 at 01:28:34AM -0500, Phil Jackson wrote: Good Point, Richard - sensible normalization - if duplicating a value here and there saves you from having to reference 12 tables instead of 3, yes, by all means. Also, adapting some standards as to using meaningfull names for columns - if fieldWidgetSize is char(25) - don't call it something else if it appears elsewhere - and don't change it's type! Phil J. Don't, however, go overboard with trying to normalize your database. Don't get me wrong: normalization is good because it saves disk and memory space (and is quite elegant as well); however, too much normalization can come at a price in PHP in terms of application speed and server overhead (not to mention creating coding nightmares if you're using your web-based application to enter data into your database as well as pull information from it). I asked this a year or so ago, but never did receive a "practical" reply that I could understand. So I'll give it another shot... What are the "nuts-and-bolts" of normalization? In a "practical" sense, how do you guys go about doing it? Do you create a spreadsheet or something, and start creating 'test' tables and see how they pan out? I understand what the 'goal' of normalization is suppose to be, I just never stumbled on a method of achieving it. Know what I mean? Tia... -- -duke Calgary, Alberta, Canada -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Designing a database
Duke, The best thing is to get a good book on the subject. There is 1st normal form, 2nd normal formetc. (quite a few). I would think that 1st normal form should do (i.e. no repeating groups as in address1, address2...addressN) Look at how the data relates to each other. The nature of relationships - one to one, one to many, many to one. Example - database of students, classes, and class schedules - at minimum you would have a table of student data, a table of all course offerenings as classes, and a table that associated a student with a particular class for a particular date/time. Take that further, you might want to break out the class descriptions, etc. into a separate table, associate by the course-id, and perhaps a section number uniquely identifies a class, ex. - Computer Science 302 at 10:00 am MWF... The key is to create your tables so that there is maximum flexiblity, but keep it simple enough that you don't have to join 30 tables together to display one studen't class schedule... Phil J. Duke Normandin wrote: I asked this a year or so ago, but never did receive a "practical" reply that I could understand. So I'll give it another shot... What are the "nuts-and-bolts" of normalization? In a "practical" sense, how do you guys go about doing it? Do you create a spreadsheet or something, and start creating 'test' tables and see how they pan out? I understand what the 'goal' of normalization is suppose to be, I just never stumbled on a method of achieving it. Know what I mean? Tia... -- -duke Calgary, Alberta, Canada -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Designing a database
Designing DB's is an entire domain of study in itself! However the rules (guidelines rather) I stick by at present but am open to new ones as I need them,- are: * Don't repeat data across tables (leads to 'data redundancy' = more complex to update the DB) * If in doubt 'put it' in a new table * Use useful and memorable names for your tables and table columns I do: tablename_colname Good luck! Russ #---# "Believe nothing - consider everything" "Web Developers do it on-the-fly." Russ Michell Anglia Polytechnic University Webteam www.apu.ac.uk/webteam [EMAIL PROTECTED] +44 (0)1223 363271 ext 2331 www.theruss.com #---# -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Designing a database
So if you could give me some tips and pointers for what tables to create etc I would be grateful. You can find a few articles on www.devshed.com about database normalization. The whole idea is to put every piece of data only once in your database. And refer to them as an ID. Every ID must be unique and should never change.. so someones postalcode is a very bad idea. You can think of multiple designs.. I'm not going to do it for you.. It will and does take some time and.. unfortunately I'm not having lots of it :( To start you need to write down all data you want to add.. and what you want to be able to pull out of the database.. I found some good pointers in the book MySQL written by Paul DuBois. It handles MySQL and connections to MySQL databases.. Have fun.. B. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
