RE: MySQL database design documentation
Hello, Two admin tools to check out if you haven't already... PHP, you can try PHPMyAdmin - http://www.phpmyadmin.net/home_page/index.php Non-PHP, try MySQL's GPL MySQL Administrator - http://dev.mysql.com/downloads/administrator/index.html However, they too may not be suitable for remote admin depending on your setup and security needs. Thanks, Jimmy Guerrero, Senior Product Manager MySQL Inc, www.mysql.com Houston, TX USA Phone: (713) 636-9239 -Original Message- From: Maurice van Peursem [mailto:[EMAIL PROTECTED] Sent: Thursday, January 05, 2006 3:37 PM To: mysql@lists.mysql.com Subject: MySQL database design documentation Hi, I'm sure this is a stupid question, but I haven't been able to find it myself. Surely there must be a free PHP utility to web-administrate a MySQL database? I use CocoaMySQL (http://cocoamysql.sourceforge.net/) on my own Mac, but it isn't suitable for online databases. Can anyone lead me in the right direction? Thanks, Maurice van Peursem The Netherlands -- 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: MySQL database design documentation
- Original Message - From: Maurice van Peursem [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, November 27, 2005 6:33 PM Subject: MySQL database design documentation Hi, I'm relatively new to the database-scene. I've installed MySQL on Mac OSX 10.3, which was easy. I've installed Perl support for MySQL, which was suprisingly difficult. I've installed CocoaMySQL (http://cocoamysql.sourceforge.net/) to create, inspect and backup databases. And now I'm building my first database, and that is not as easy as I had hoped. I know that use of the 'JOIN' keyword can save me pages of Perl code, but how it works exactly is not yet clear to me. Therefore I'm looking for a book, or maybe other documentation (on the web?), that can point me in the right direction. More specifically, I'm looking for a book that explains how to design and build databases, with examples of the queries in MySQL. Most books describe how you install MySQL, and list the SQL commands, but this information I already have. Can any of you suggest to me some helpful learning material? For the most part, _any_ good database design book for _any_ decent relational database should do the job for you. That's because all (?) of the professional grade databases use the same SQL and the same normalization techniques to decide what columns belong in what tables and what primary and foreign keys should be used. Therefore, a good design book for DB2 or Oracle or Sybase would probably tell you almost exactly the same things as a good design book specifically intended for MySQL. You will still need to use the MySQL reference to help you with places where the MySQL syntax is slightly different than the syntax used by the other database but this really shouldn't happen too often. However, if you want a design book specifically written for MySQL, you may want to look at http://www.informit.com/articles/article.asp?p=30885rl=1. I should stress that I don't have this book, nor have I read it cover to cover. But the sample chapter on database design is pretty good, so, if the rest of the book is as good, you should come out okay. In fact, you may find that the sample chapter alone, which you can read online for free, may tell you everything you really want to know and save you the cost of the book. No guarantees on that but it's a starting point anyway. By the way, I have not seen any other MySQL Design books so there may be others that are better. The URL I've given you actually mentions some other books specifically for MySQL that may suit your personal learning style better. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 25/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database design documentation
A couple of good links for databases. Database Design (quick and dirty, but gets the points across): http://www.geekgirls.com/menu_databases.htm - the from scratch side SQL: Basics: http://www.sqlcourse.com (you probably already know this stuff - but just in case. semi-Advanced: http://sqlcourse2.com (joins are specifically at http://sqlcourse2.com/joins.html). Maurice van Peursem wrote: Hi, I'm relatively new to the database-scene. I've installed MySQL on Mac OSX 10.3, which was easy. I've installed Perl support for MySQL, which was suprisingly difficult. I've installed CocoaMySQL (http://cocoamysql.sourceforge.net/) to create, inspect and backup databases. And now I'm building my first database, and that is not as easy as I had hoped. I know that use of the 'JOIN' keyword can save me pages of Perl code, but how it works exactly is not yet clear to me. Therefore I'm looking for a book, or maybe other documentation (on the web?), that can point me in the right direction. More specifically, I'm looking for a book that explains how to design and build databases, with examples of the queries in MySQL. Most books describe how you install MySQL, and list the SQL commands, but this information I already have. Can any of you suggest to me some helpful learning material? Thanks, Maurice van Peursem The Netherlands -- life is a game... so have fun. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database design documentation
Relational Database Design Clearly Explained, Second Edition ISBN: 1558608206 The original edition was my first primer on relational databases. It was an excellent read. Ben Maurice van Peursem wrote: Hi, I'm relatively new to the database-scene. I've installed MySQL on Mac OSX 10.3, which was easy. I've installed Perl support for MySQL, which was suprisingly difficult. I've installed CocoaMySQL (http://cocoamysql.sourceforge.net/) to create, inspect and backup databases. And now I'm building my first database, and that is not as easy as I had hoped. I know that use of the 'JOIN' keyword can save me pages of Perl code, but how it works exactly is not yet clear to me. Therefore I'm looking for a book, or maybe other documentation (on the web?), that can point me in the right direction. More specifically, I'm looking for a book that explains how to design and build databases, with examples of the queries in MySQL. Most books describe how you install MySQL, and list the SQL commands, but this information I already have. Can any of you suggest to me some helpful learning material? Thanks, Maurice van Peursem The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql database design.
You can find a number of good starting point data models here: http://www.databaseanswers.com/data_models You might also check out some of the applications on sites like www.hotscripts.com and www.sourceforge.net to see how they have structured their database for similar projects. (or maybe you will find something ready to use). olinux --- Brian Duke [EMAIL PROTECTED] wrote: I need a little help in constructing an order tracking database. We've decided to use MySQL mostly because it's the best supported database out in the wild. Does anyone have an example of an order tracking datamap? A link to a site with the basic flowchart would be a great help. The application is an everyday drycleaner shop. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Database Design
Hi Brian,
By no means am I a MySQL guru (or any other database server environment
guru, for that matter), but could you simply have a reference table that
indicates the percentage of the grape used in the relevant wine?
So, you might have three tables, Wines, GrapeVariety,
Wines_GrapeVarietiesUsed (or whatever).
In Wines, you record the details of the wine in question. In
GrapeVariety you record the details of the different grapes. In
Wines_GrapeVarietiesUsed, you record the key from the Wine table, the
key from the GrapeVariety table, and the percentage of the grape
variety.
As an example:
Wines:
Wineid, winename, winedescription
1, wine1, wine description 1
2, wine2, wine description 2
3, wine3, wine description 3
GrapeVariety:
Varietyid, varietyname, varietydescription
1, variety1, variety description 1
2, variety2, variety description 2
3, variety3, variety description 3
And then in Wines_GrapeVarietiesUsed:
Wineid, varietyid, percentage
1, 1, 1
2, 2, 1
3, 1, 0.2
3, 2, 0.3
3, 3, 0.5
In this example we have 3 bottles of wine and three varieties. Wines 1
2 use 100 percent (i.e. 1) of varieties 1 and 2 respectively, whereas
wine 3 uses all three grape varieties with 20 percent of variety1, 30
percent of variety2 and 50 percent of variety3.
To perform a query that would depict all of this in a single resultset,
you might do something like:
SELECT w.winename, w.winedescription, v.varietyname,
v.varietydescription, gv.percentage FROM wines w, GrapeVariety v,
Wines_GrapeVarietiesUsed gv WHERE w.wineid = gv.wineid AND v.varietyid =
gv.varietyid;
What this delivers is a recordset that looks something like:
'wine1','wine description 1','variety1','variety description 1','1'
'wine2','wine description 2','variety2','variety description 2','1'
'wine3','wine description 3','variety1','variety description 1','0.2'
'wine3','wine description 3','variety2','variety description 2','0.3'
'wine3','wine description 3','variety3','variety description 3','0.5'
Below are the CREATE TABLE and INSERT INTO statements I used to build
this example.
Hope this helps a little,
All the best,
MW
CREATE TABLE Wines (wineid INT(10) unsigned NOT NULL auto_increment,
winename VARCHAR(100), winedescription TEXT, PRIMARY KEY (`wineid`))
TYPE = MYISAM;
CREATE TABLE GrapeVariety (varietyid INT(10) unsigned NOT NULL
auto_increment, varietyname VARCHAR(100), varietydescription TEXT,
PRIMARY KEY (`varietyid`)) TYPE = MYISAM;
CREATE TABLE Wines_GrapeVarietiesUsed (wineid INT(10) unsigned NOT NULL,
varietyid INT(10) unsigned NOT NULL, percentage float NOT NULL default
'0') TYPE=MYISAM;
INSERT INTO wines (winename, winedescription) VALUES ('wine1', 'wine
description 1');
INSERT INTO wines (winename, winedescription) VALUES ('wine2', 'wine
description 2');
INSERT INTO wines (winename, winedescription) VALUES ('wine3', 'wine
description 3');
INSERT INTO GrapeVariety (varietyname, varietydescription) VALUES
('variety1', 'variety description 1');
INSERT INTO GrapeVariety (varietyname, varietydescription) VALUES
('variety2', 'variety description 2');
INSERT INTO GrapeVariety (varietyname, varietydescription) VALUES
('variety3', 'variety description 3');
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (1,1,1);
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (2,2,2);
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (3,1,.2);
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (3,2,.3);
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (3,3,.5);
-Original Message-
From: Colaluca, Brian [mailto:[EMAIL PROTECTED]]
Sent: Friday, 10 January 2003 6:56 AM
To: [EMAIL PROTECTED]
Subject: MySQL Database Design
I am in the midst of designing a personal database for keeping track of
wines. After perusing through several beginner books on MySQL and PHP,
I
have decided that my next step would be to embark upon database design.
My design is almost complete and normalized, although I do expect to be
making many tweakings as my knowledge progresses.
I have come to a brick wall on one facet of my design, however. I've
come
to understand that having a lot of NULLs in your database may be a sign
of a
poor design. And yet I'm having a problem reconciling this with the
wildly
un-uniform world of wines from around the world. For instance, I would
like
to have a table called GrapeVariety, and have the variety_id be a
primary
key. Another table would be Wine. And yet, one wine could have one
type
of grape or more.
For instance, let's say that wine XYZ has 80% GrapeA, and 20% GrapeB.
Since
my grape variety would presumably be a foreign key in the Wine table,
how
could I specify a certain *percentage* of a foreign key? I've tried
hashing
this out in numerous ways, including the addition of a Blend table
with
multiple primary keys, but anyway I slice it, there will still be an
abundance of NULLs. For
RE: MySQL Database Design
i was playing around with ideas below, so they may be worthless chatter :-) what i was thinking I'll just assume the wine world is not wildly un-uniform... and see where that gets me... :-) and wine is just an object... but the wine has many grape varieties sounds a hell of a lot like one to many in sql thanks Jim ===start chatter n = null anything else = not null all attributes analysis a1 a2 a3 a4 a5 a6 a7 wine1 x n x n x n x wine2 y x x n n x n wine3 z x x x x n n 8 nulls common attributes table parent ID a1 a3 wine1 x x wine2 y x wine3 z x special attributes table 1 child0 ID a5 a7 wine1 x x wine3 x n special attributes table 2 child1 ID a2 a6 wine2 x x wine3 x n special attributes table 3 child2 ID a4 wine3 x 2 nulls ===end chatter -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 09, 2003 6:09 PM To: Colaluca, Brian Cc: [EMAIL PROTECTED] Subject: Re: MySQL Database Design Colaluca, Brian wrote: I have come to a brick wall on one facet of my design, however. I've come to understand that having a lot of NULLs in your database may be a sign of a poor design. And yet I'm having a problem reconciling this with the wildly un-uniform world of wines from around the world. For instance, I would like to have a table called GrapeVariety, and have the variety_id be a primary key. Another table would be Wine. And yet, one wine could have one type of grape or more. Just an idea ... to get your head spinning (and some sample queries): Wine - ID int unsigned not null auto_increment primary key, Name ... Winery ... Grapes - ID int unsigned not null auto_increment primary key, Name ... Vineyard? ... GrapesInWine - WineID int unsigned not null, GrapesID int unsigned not null, Percentage int unsigned not null ... where Percentage is between 0 and 100. Then you can, to insert a wine named Foo with 50% of each Grape1 and Grape2: INSERT INTO Wine (Name) VALUES (Foo); SELECT @WinesID := last_insert_id();# I'm using server variables here for the sake of demo ... INSERT INTO Grapes (Name) VALUES (Grape1); SELECT @GrapesID := last_insert_id(); INSERT INTO GrapesInWine (WineID, GrapesID, Percent) VALUES (@WinesID, @GrapesID, 50); INSERT INTO Grapes (Name) VALUES (Grape2); SELECT @GrapesID := last_insert_id(); INSERT INTO GrapesInWine (WineID, GrapesID, Percent) VALUES (@WineID, @GrapesID, 50); Then, to find out what's in the wine named Foo: SELECT * FROM Grapes LEFT JOIN GrapesInWine ON Grapes.ID = GrapesID LEFT JOIN Wine ON WinesID = Wine.ID WHERE Wine.Name = Foo; Or, to find the amounts of Grape1 in all wines: SELECT * FROM Wine LEFT JOIN GrapesInWine ON WineID = Wine.ID LEFT JOIN Grapes ON Grapes.ID = GrapesID WHERE Grapes.Name = Grape1; -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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 - 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
RE: MySQL Database Design
Blend will be a cross reference with a one to many relationship This is very simplified but an example of your data could be: Select * from Wine; ++--+ | WineID | WineName | ++--+ | 1 | XYZ | ++--+ Select * from Grape; +-+---+ | GrapeID | GrapeName | +-+---+ | 1 | GrapeA| | 2 | GrapeB| +-+---+ Select * from Blend; +-++-++ | BlendID | WineID | GrapeID | Percentage | +-++-++ | 1 | 1 | 1 | 80 | | 2 | 1 | 2 | 20 | +-++-++ This is obviuously very simplified, just trying to give you a quick response so you can move ahead in your development without being stuck on this. There might be some disagreement on the naming conventions I have illustrated. Use what you like best. -Original Message- From: Colaluca, Brian [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 09, 2003 12:56 PM To: [EMAIL PROTECTED] Subject: MySQL Database Design For instance, let's say that wine XYZ has 80% GrapeA, and 20% GrapeB. Since my grape variety would presumably be a foreign key in the Wine table, how could I specify a certain *percentage* of a foreign key? I've tried hashing this out in numerous ways, including the addition of a Blend table with multiple primary keys, but anyway I slice it, there will still be an abundance of NULLs. For while the majority of wines may only contain one grape, there could be wines that have up to 5 or 6 in varying percentages. - 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
Re: MySQL Database Design
Colaluca, Brian wrote: I have come to a brick wall on one facet of my design, however. I've come to understand that having a lot of NULLs in your database may be a sign of a poor design. And yet I'm having a problem reconciling this with the wildly un-uniform world of wines from around the world. For instance, I would like to have a table called GrapeVariety, and have the variety_id be a primary key. Another table would be Wine. And yet, one wine could have one type of grape or more. Just an idea ... to get your head spinning (and some sample queries): Wine - ID int unsigned not null auto_increment primary key, Name ... Winery ... Grapes - ID int unsigned not null auto_increment primary key, Name ... Vineyard? ... GrapesInWine - WineID int unsigned not null, GrapesID int unsigned not null, Percentage int unsigned not null ... where Percentage is between 0 and 100. Then you can, to insert a wine named Foo with 50% of each Grape1 and Grape2: INSERT INTO Wine (Name) VALUES (Foo); SELECT @WinesID := last_insert_id();# I'm using server variables here for the sake of demo ... INSERT INTO Grapes (Name) VALUES (Grape1); SELECT @GrapesID := last_insert_id(); INSERT INTO GrapesInWine (WineID, GrapesID, Percent) VALUES (@WinesID, @GrapesID, 50); INSERT INTO Grapes (Name) VALUES (Grape2); SELECT @GrapesID := last_insert_id(); INSERT INTO GrapesInWine (WineID, GrapesID, Percent) VALUES (@WineID, @GrapesID, 50); Then, to find out what's in the wine named Foo: SELECT * FROM Grapes LEFT JOIN GrapesInWine ON Grapes.ID = GrapesID LEFT JOIN Wine ON WinesID = Wine.ID WHERE Wine.Name = Foo; Or, to find the amounts of Grape1 in all wines: SELECT * FROM Wine LEFT JOIN GrapesInWine ON WineID = Wine.ID LEFT JOIN Grapes ON Grapes.ID = GrapesID WHERE Grapes.Name = Grape1; -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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
Re: MySQL database design, one column, 10 entries?
You need to add an additional table, favorites. It should have three columns: favoriteID INT, userID INT, favoriteTypeID INT REFERENCES FavoriteType. Plus the additional column for the rating, assuming it's associated with a favorite. Each row represents a single preference (this way users can have an arbitrary number of preferences). Each user would be associated with multiple rows in the table. The favoriteID is so that you can delete/update favorites easily; you want to be able to talk about a particular row. You might want a SELECT like this: SELECT * FROM favorites WHERE userID = $id ORDER BY rating DESC LIMIT 0,10 to get the top ten favorites for user identified by $id. You might also want to make an additional table, favoriteType, unless each favorite is completely unique (in which case the third column in the table above would have VARCHAR or TEXT type). (You should avoid that kind of design if possible, because most likely anything you want to rank is going to have similarities across users) favoriteTypeID INT favoriteLabel VARCHAR You should do some research on normalization and database design. There are some good books on the topic. Quinten On Sun, 11 Aug 2002, david wrote: Date: Sun, 11 Aug 2002 00:43:14 -0400 From: david [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: MySQL database design, one column, 10 entries? I am creating several tables in MySQL and linking via primary keys. I am held up on one issue, for one row in one table i have a column 'favorites' where i want to hold up to 10 unique entries, how do i implement this? userTable userId varchar(20) name varchar(30) email varchar(40) preferences userId varchar(20) styles varchar(20) favorites preferences userId styles favorites rating 01 'modern''#1 sleek' 15 '#2 ultra-sleek'20 '#3 un-sleek' 12 '#4 plain' 9 etc, up to 10 or so. 02 'gothic''#1 dark' 21 '#2 tall' 4 '#3 scary' 2 etc, etc,... I can't just make it a really long varchar(1000) because of the other columns that act on the same data. I hope that this is an appropriate question for this MySQL list. TIA -david - 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 - 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
Re: MySQL database design
At 8/11/2002, you wrote: MySQL queries.. tables... design. http://garnet.acns.fsu.edu/~tlr7425/my_tables.gif There you will see a rough draft of what I am trying to do. Perhaps you will see some places that I will need to use a table_map? Or you can advise me of how to arrange my keys, or otherwise develop this db? I'm learning and need this help to better understand keys and normalization, while developing a db that can withstand expansion. I understand that it looks like I am trying to normalize what appears to be almost all the way, I could be wrong about that; but this is what I am interested in. Hi Lloyd, Spending plenty of time designing the database is be the best decision you can make. If the base is solid and flexible you'll end up with a solid and flexible application and not rewriting database structure will every small feature request. Advice from some gurus on something that *I am working on* would help me to understand much better than all the books I have! (I was on this list before, but I had to change my subscription address.) Thanks in Advance, Lloyd I'm no guru, but I'd like to tell you my view on this as about a year ago I struggled same battle and I think I learned a lot from it. Real gurus out there please feel free to correct my writings :) The design should always be related to what the relations there are between items you describe with the database. Here I see you are building a database of personnel and gear they use, groups they belong to and dirs they can access etc. But not much relativity there now. When normalization is on level 5 you see mostly id's :) Ask questions. Think examples of queries you will have to do. Ask yourself what if someone asks me to pull out data that has . Build and test queries to get that data. Make assumption that there is a LOT of data. Think space usage. The questions I would ask about your db structure are e.g.: Will data be repeated (in a table)? - there is now a lot of repeated data. A computer table could hold just model id and serial, and then have id's for cpu, hdd, ram, ports, manufacturer etc. An IP table could hold IP's, and there is not much point storing an IP with a computer as one computer will definitely have more than one IP's. Same way: OSes table could be OSes osID brandID displaynameID date_added date_modified or with even more normalization OSes osID osdataID .and with that you'd have an extra table. OSes osdataID os_name os_manufacturer os_release_data os_price os_etc plus to that you'd have a separate table OSes_to_comps OSes_to_comps_id compID osID date_added date_modified .which will let you have one computer have more than one OS (perfectly possible). 'OSes_to_comps_id' is really not needed but I like to have it there anyway. More questions: How do you link a person to a device? Or several persons to same device? Can one person have more than one device? What if one person has two computers and 4 displays? How do you link a device to a group (list all devices of a group) How do you link gear together? Answer to all above: use intermediate tables, like the 'OSes' example above. I would do a table 'person_to_computer' which would have 'PersID' and 'compID'. This way one person can have several computers. Using same method: 'person_to_display' table would just link a person to a display. These intermediate tables take very little room (just use two int cols or so - use same int lenght as you autoincrement keys) and make searching e.g. what displays a person has? much easier. They also make it possible to have complex relationships with minimal effort. SELECT displays.make, displays.model, personnel.firstName, personnel.lastName FROM displays, personnel, personnel_to_display WHERE personnel_to_display.dispID = displays.dispID AND personnel_to_display.persID = personnel.persID Display detail could be splitted to several tables just like in OSes example (you could build manufacturer and model tables which are shared accross the database), so that you could get display info by SELECT manufacturer.manufacturer_id manufacturer.manufacturer_name, model.model_id model.model_name FROM manufacturer, model, displays WHERE displays.manufacturer_id = manufacturer.manufacturer_id AND displays.model_id = model.model_id Heavy normalization will most likely make programming the application more complex, but it will pay off in speed and flexibility to change and add things. PS. About table names: - mixing case will make you make
Re: MySQL database design
on 8/11/02 2:47 PM, Pekka Saarinen, typed: At 8/11/2002, you wrote: MySQL queries.. tables... design. http://garnet.acns.fsu.edu/~tlr7425/my_tables.gif There you will see a rough draft of what I am trying to do. Perhaps you will see some places that I will need to use a table_map? Or you can advise me of how to arrange my keys, or otherwise develop this db? I'm learning and need this help to better understand keys and normalization, while developing a db that can withstand expansion. I understand that it looks like I am trying to normalize what appears to be almost all the way, I could be wrong about that; but this is what I am interested in. Hi Lloyd, Spending plenty of time designing the database is be the best decision you can make. If the base is solid and flexible you'll end up with a solid and flexible application and not rewriting database structure will every small feature request. Advice from some gurus on something that *I am working on* would help me to understand much better than all the books I have! (I was on this list before, but I had to change my subscription address.) Thanks in Advance, Lloyd I'm no guru, but I'd like to tell you my view on this as about a year ago I struggled same battle and I think I learned a lot from it. Real gurus out there please feel free to correct my writings :) The design should always be related to what the relations there are between items you describe with the database. Here I see you are building a database of personnel and gear they use, groups they belong to and dirs they can access etc. But not much relativity there now. When normalization is on level 5 you see mostly id's :) Ask questions. Think examples of queries you will have to do. Ask yourself what if someone asks me to pull out data that has . Build and test queries to get that data. Make assumption that there is a LOT of data. Think space usage. The questions I would ask about your db structure are e.g.: Will data be repeated (in a table)? - there is now a lot of repeated data. A computer table could hold just model id and serial, and then have id's for cpu, hdd, ram, ports, manufacturer etc. An IP table could hold IP's, and there is not much point storing an IP with a computer as one computer will definitely have more than one IP's. Same way: OSes table could be OSes osID brandID displaynameID date_added date_modified or with even more normalization OSes osID osdataID .and with that you'd have an extra table. OSes osdataID os_name os_manufacturer os_release_data os_price os_etc plus to that you'd have a separate table OSes_to_comps OSes_to_comps_id compID osID date_added date_modified .which will let you have one computer have more than one OS (perfectly possible). 'OSes_to_comps_id' is really not needed but I like to have it there anyway. More questions: How do you link a person to a device? Or several persons to same device? Can one person have more than one device? What if one person has two computers and 4 displays? How do you link a device to a group (list all devices of a group) How do you link gear together? Answer to all above: use intermediate tables, I believe that what you called intermediate table books are calling table map, or something with the word map in it. What you have done here is extremely helpful. You don't know how much I appreciate it, especially when I was feeling so ignored -almost like I was banned or something! Thank you, thank you, and thank you again. like the 'OSes' example above. I would do a table 'person_to_computer' which would have 'PersID' and 'compID'. This way one person can have several computers. Using same method: 'person_to_display' table would just link a person to a display. These intermediate tables take very little room (just use two int cols or so - use same int lenght as you autoincrement keys) Could you please elaborate a bit on, ...use same int as you autoincrement keys... -for example which keys should (or must be?) be autoincremented? and make searching e.g. what displays a person has? much easier. They also make it possible to have complex relationships with minimal effort. SELECT displays.make, displays.model, personnel.firstName, personnel.lastName FROM displays, personnel, personnel_to_display WHERE personnel_to_display.dispID = displays.dispID AND personnel_to_display.persID = personnel.persID Display detail could be splitted to several tables just like in OSes example (you could build manufacturer and model tables which are shared accross the database), so that you could get display info by SELECT
Re: MySQL database design
At 8/11/2002, you wrote: I believe that what you called intermediate table books are calling table map, or something with the word map in it. Hi Lloyd, Books are nice to have (you can lie down to a sofa or sit in the loo with a book), and what I started with (SAMS Teach Yourseft MySQL in 21 days) are great to start with, but they don't get you very far in design process. The best resource is to design things out on a paper and coding tests on real data. What you have done here is extremely helpful. You don't know how much I appreciate it, especially when I was feeling so ignored -almost like I was banned or something! Thank you, thank you, and thank you again. Np. Nice to hear it was useful! :) Could you please elaborate a bit on, ...use same int as you autoincrement keys... -for example which keys should (or must be?) be autoincremented? I meant to say that if you refer to a key from table A from another table (B) you must use the same data type in both. If you have an autoincrement key int(11) on one table and you use it as int(5) on some intermediate table you'll end up having problems pretty soon. There should be (can be?) only one autoincrement key per table. Autoincrements are useful for stuff that you need to refer later by id, like adding to a list of photographs, list of car parts - the autoincrement system takes cares you don't have duplicate ids and that whenever you insert a new item you'll have a new unique key. Heavy normalization will most likely make programming the application more complex, but it will pay off in speed and flexibility to change and add things. I needed to be told that. because books seem to want you to shy away from over-normalization (beyond level 3). Well, the books don't tell much about normalizing much because the big companies take a lot of money to do it for you :) I think other reason is that writers think that keeping focus too long on one subject is not good for the reading process. Who knows. But it is not hard to normalize, and actually the further you go the more clearly you'll see the real data in your database. Pekka - Pekka Saarinen http://photography-on-the.net - - 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
Re: MySQL database design, one column, 10 entries?
I am not very sure I understand the questionbut maybe column type 'enum' is what you are looking for. - Original Message - From: david [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, August 10, 2002 9:43 PM Subject: MySQL database design, one column, 10 entries? I am creating several tables in MySQL and linking via primary keys. I am held up on one issue, for one row in one table i have a column 'favorites' where i want to hold up to 10 unique entries, how do i implement this? userTable userId varchar(20) name varchar(30) email varchar(40) preferences userId varchar(20) styles varchar(20) favorites preferences userId styles favorites rating 01 'modern''#1 sleek' 15 '#2 ultra-sleek'20 '#3 un-sleek' 12 '#4 plain' 9 etc, up to 10 or so. 02 'gothic''#1 dark' 21 '#2 tall' 4 '#3 scary' 2 etc, etc,... I can't just make it a really long varchar(1000) because of the other columns that act on the same data. I hope that this is an appropriate question for this MySQL list. TIA -david - 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 - 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
