Hi Skip, See my remarks interspersed in your note.
By the way, I'm copying the mailing list so that others can benefit from the discussion, either now or somewhere down the road via the archives. Rhino ----- Original Message ----- From: "Skip Taylor" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Sent: Sunday, October 17, 2004 2:49 AM Subject: Re: Can MySQL do this? > Hello Rhino, > > Thank you for taking the time to answer my question. > My pleasure! > You are correct in your assumption that "index=key" and > "link=join/lookup". > > The last time I worked with databases, it was in the late 1980's and > early 1990's. I wrote the entire system or used a rapid design tool > called SIMPLE (System IMPLementation by Example). The terms > I used were from what I used at that time. > I'm not that surprised that older systems used different terms like "index" and "join/lookup". In fact, when I first started learning the theory behind relational databases - also in the late 1980s - I found that the relational theorists were using rather different vocabulary like 'tuples' and 'relations'. Somehow, those transmuted into different but more widely used terms but I'm not sure how or why. I'm guessing that the marketing people for the first relational databases wanted to use terms that were a little more commonplace and a little less daunting to beginners. And, of course, everyone was a beginner to relational databases in those days. > Your terms make more sense to me and that may indicate my > difficulty with learning MySQL. I need to let go of the archaic and > embrace the new! > I don't really think you'll have a great deal of difficulty learning MySQL. The concepts underlying relational databases really aren't that hard. There will be a bit of new terminology but I think you'll find a lot of it maps very precisely to things you already know from your earlier work; you just have to learn the new term for it. At first you'll probably do mental translations - think 'index' when someone says 'key' - but eventually, you'll probably just think 'key'. > After reading your message twice, I think I could actually remove > the US_States table and write the state list only into the HTML > code. Perhaps it would simplify matters. > If you know all your state codes by heart and all of your users do as well, you can probably get away with making the US_States table have only the state_code column. Personally, I tend to get muddled up with the 'A' states; for instance, when I was trying to remember the state code for Alaska, I first wrote 'AL', then remembered that 'AL' is Alabama. Mind you, I'm Canadian so I have an excuse ;-) However, I wouldn't get rid of the US_States table altogether (or handle it with application code) if I were you. Tables like this, often called lookup tables, can be very handy as a means of enforcing the integrity of your data. Remember, your US_Cities table will contain a state code for each city in the table. If you define the state code column of the US_Cities table as a foreign key of the US_States table, this simple step will ensure that the only value which can possibly be entered in the state code column of US_Cities is one of the values in the US_States table. That may not sound like a big deal but it is. Remember, there are many ways to access your database, including programs, command lines, and scripts. While a program could manually check the input from an insert statement and make sure that the state code matches an internal array of state codes, a script or command line don't have the same luxury; anyone doing an insert from the script or command line could easily mess up and use an invalid state code. Then, you'll have bad data in your US_Cities table. Just imagine the confusion when your users see New York, NV instead of New York, NY! Foreign keys are a very valuable aspect of relational databases and you should use them whenever you can. They completely eliminate the need for any application code to do the checking!! However, I should mention that you will need to use the InnoDB "engine" in order to be able to define them and enforce them automatically. Don't worry, that's no big deal. As I recall, InnoDB is installed along with the other "engines" so it should be available on your system already. All you should have to do is add "Type=InnoDB" to your Create Table statement, like this: create table US_States (state_code char(2) not null, state_name char(20) not null, primary key(state_code)) Type=InnoDB; > I'm curious. The language I've seen in MySQL queries reminds me > of an operating system of long ago named PICK. The operating > system was a database. I wonder if it got a start from that? > I have heard of PICK and knew that it was an operating system but I've never used it or learned any of its terminology. Relational databases originally came out of the IBM research labs in the 1970s as a result of work published by Dr. E.F. Codd, a British mathematician who died just last year. This article about him, http://www.dbdebunk.com/page/page/621965.htm, gives you some idea of what he did and why it was important. I don't see anything in there about PICK or SIMPLE but it's not inconceivable that he borrowed some of the PICK terms for use in MySQL. Then again, as I said before, Codd's terminology of 'relations' and 'tuples' transmuted into other terms for common use. The people who adopted terms like 'key' and 'rows' may have borrowed from PICK for all I know. Or, more likely, the PICK people and the people who marketed relational databases both borrowed their terms from some other existing product, like IMS (IBM's hierarchical database). I'll leave that for the computer historians to research ;-) > Skip > > On 16 Oct 2004 at 20:38, Rhino wrote: > > > > ----- Original Message ----- > > From: "Skip Taylor" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Saturday, October 16, 2004 5:08 PM > > Subject: Can MySQL do this? > > > > > > > Hello, > > > > > > I'm new to MySQL, SQL in general and even PHP which may be needed to do > > this. > > > My intention is to implement this on an Internet webpage. > > > > > > I want to set up a database for listing people in cities for various > > specialties with a > > > time limit on their availability (date limited). > ... > > You are using two common words in non-standard ways and that is causing me a > > bit of confusion. Those two words are "index" and "link". I *think* you mean > > to say "key" instead of "index" and "join" (or maybe "lookup") instead of > > "link"; I'll explain that in a minute. IF those are reasonable substitutions > > on my part, then you should have no trouble getting MySQL to accomodate your > > database design. > > > > In your table descriptions, you have an "index" in each of your tables. I > > think you should use the term "key". It might sound nitpicky but an index is > > always built on a table column but it is not a table column itself. > > > > For example, given your first table, I would probably create it as follows: > > > > create table US_States > > (state_code char(2) not null, > > state_name char(20) not null, > > primary key(state_code)); > > > > The contents of the table would then be: > > state_code state_name > > ------------ ------------- > > AL Alabama > > AK Alaska > > NH New Hampshire > > > > > > The state_code column is serving as a key. Your queries to find the > > description for a given state will seach on the state_code column. For > > example: > > > > select state_name > > from US_States > > where state_code = 'AL'; > > > > should return 'Alabama'. > > > > An index, properly speaking, is a structure that is separate from the table > > itself, i.e. a separate file, which you can choose to construct to improve > > the performance of queries like the one I just gave in my example. The index > > is created with a separate statement like this: > > > > create unique index state_ix on US_States(state_code); > > > > You are not required to build indexes but they are frequently a good idea if > > you don't overdo them. > > > > The other term that you are misusing is "link". I think you mean "join" or > > maybe "lookup" where you said "link". For example, if you want to know the > > full name of a state that is referenced in the US_Cities table, you will use > > a join to do a lookup of that information in the US_States table. Therefore, > > if the US_Cities table looks like this: > > > > state_code city_name > > ------------ ------------ > > AL Springfield > > AK Anchorage > > NH Springfield > > > > This query will determine the full name of each state that contains a city > > named Springfield. > > > > select city_name, state_name > > from US_States s inner join US_Cities c on s.state_code = c.state_code > > where c.city_name = 'Springfield' > > > > Which should give this result: > > > > city_name state_name > > ------------- -------------- > > Springfield Alabama > > Springfield New Hampshire > > > > The process of finding something like the full state name that corresponds > > to a state code is usually called "doing a lookup". The technique for doing > > a lookup in SQL is a "join": the 'from US_States s inner join US_Cities c on > > s.state_code = c.state_code' line is the part of the statement that combines > > the two tables where the state codes match; this is called a join. > > > > Again, I don't say these things to be pedantic, I'm just trying to put this > > information in terms that most experienced database people will understand. > > > > As I said before, if this is what you mean, you should have no great > > difficulty creating a MySQL database to do what you want. > > > > How long that will take is another matter. I've been doing database stuff > > like this for 20 years so I could build what you are describing in a few > > hours. If you're new to SQL and database, it might take you days or weeks, > > depending on if you are a quick study or not and how much help you get. The > > first time through anything can be quite painful due to the learning curve, > > as you probably know from other things you've learned. The good news is that > > database work is, for the most part, pretty logical so it shouldn't be too > > hard to get your head around. > > > > > Is there a database generation system that would be able to handle this > > task and > > > generate a webpage as output? > > > > > As for tools that would generate your tables and PHP code, I really can't > > say. I write my own SQL by hand (and by choice) and execute it from the > > command line and/or scripts; I also write my own Java code to display web > > pages. > > > > However, I believe Stuart Felenstein, another relative newbie on this list, > > is using a tool much like you describe so I am taking the liberty of copying > > him with this reply. Stuart, when you get this, can you please respond to > > Skip with information about the tool you are using? > > > > Rhino > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
