Page 1 of 1
SQL: A few questions...
Posted: Mon Nov 16, 2009 7:48 pm
by carleihar
Alright, I have a few questions, and they may seem silly, but I'm new to PHP/SQL so forgive me.
Is there a way that one colum can be updated in related to another column automatically?
Say I wanted a column for "days registered". If I have one column with the date registered, the column "days registered" should add one each day.
Also, can a column hold more than one item?
Say I wanted a column for "awards" for each user, can I keep a list of them in one column/row?
One more question (sorry!): I've been thinking about this for a while, but I just can't seem to figure out how to do it. First, I have a SQL database with a list of horses and their unique characteristics. I have made a program that takes information from two horses and uses the information to create a totally new horse. One characteristic I am stuck on is breed. I would like to show the percentage of each breed that is in the horse. So if a 50% Quarter Horse and a 50% Throughbred is combined with a 100% Warmblood, it should show percentages accordingly. Firstly, how would the math work for this, and secondly how would I store all these (each breed and its corresponding percentage) in a table?
Thanks everyone!
Re: SQL: A few questions...
Posted: Tue Nov 17, 2009 6:39 am
by iankent
You need to consider using multiple tables with relationships.
carleihar wrote:Say I wanted a column for "days registered". If I have one column with the date registered, the column "days registered" should add one each day.
No need to - you can easily calculate the number of days registered using the date column on-the-fly. No point in storing the additional redundant data. Either use PHP to calculate the number of days since registration or use MySQL to do it as part of the query.
carleihar wrote:Also, can a column hold more than one item?
Say I wanted a column for "awards" for each user, can I keep a list of them in one column/row?
A column can hold more than one item by using a delimiter, e.g.:
a/b/c/d/e/f/g
which you can then split in PHP using explode(). However, definately not recommended! Personally I'd create another two tables:
Table A - users
Table B [new] - awards
Table C [new] - awards to users
Table B would simply list the available awards with an id (example columns: id, awardname)
Table C would simply list the awards that a particular user has (example columns: userid, awardid)
You can then retrieve a list of awards for users like this:
SELECT * FROM users u INNER JOIN awards_to_users a ON (u.userid = a.userid) INNER JOIN awards b ON (a.awardid=b.id)
carleihar wrote:
One more question (sorry!): I've been thinking about this for a while, but I just can't seem to figure out how to do it. First, I have a SQL database with a list of horses and their unique characteristics. I have made a program that takes information from two horses and uses the information to create a totally new horse. One characteristic I am stuck on is breed. I would like to show the percentage of each breed that is in the horse. So if a 50% Quarter Horse and a 50% Throughbred is combined with a 100% Warmblood, it should show percentages accordingly. Firstly, how would the math work for this, and secondly how would I store all these (each breed and its corresponding percentage) in a table?
Thanks everyone!
Again, I'd use multiple tables - one for the users, one for the breeds, another for the horses, a table for the horse to user relationship and another for the horse to breed relationships. The horse to breed table would link particular breeds to each actual horse, and could then store the percentages of each breed that makes up the horse. Similar to the problem above. I'll expand if you need me to but hopefully if you understand the example above, a bit more thought should make this one obvious
hth
Re: SQL: A few questions...
Posted: Tue Nov 17, 2009 11:54 am
by carleihar
Thanks for the help!
I'm still curious about updating tables automatically. I realize that its not necessary in the "days registered" function but what about something else? Is it possible to update the table automatically?
In a silly example:
What if I had a dog that was last fed on Tuesday.
I could have a column that would have days since it was fed.
Can I have another column with "hunger" that correlates with the days since it was fed?
This would make it much easier for making a page where you can "feed the dog" and update the hunger.
Thanks again for all your help!
Re: SQL: A few questions...
Posted: Tue Nov 17, 2009 12:02 pm
by iankent
carleihar wrote:In a silly example:
What if I had a dog that was last fed on Tuesday.
I could have a column that would have days since it was fed.
Can I have another column with "hunger" that correlates with the days since it was fed?
This would make it much easier for making a page where you can "feed the dog" and update the hunger.
Same principle - all you need to do is store the date/time the dog was last fed. From that you could then calculate how many days ago it was last fed, and consequently how hungry it is.
There's no way to auto-update data in a table with one exception, and thats a TIMESTAMP column which will update itself to the date/time the row was last updated, but even then its not truly automatic as you need to run an UPDATE query that actually changes some data on that row.
In your dog example, say the dog gets hungrier at a rate of 10% per day, and so after 10 days it dies of hunger. You could get all three values (date last fed, days since last fed and hunger percentage) as follows:
SELECT date_fed, DATEDIFF(NOW(), date_fed) AS days_since_fed, days_since_fed/10 as hunger_percentage FROM table;
So, on the same day the dog has been fed, that would return:
todays date, 0, 0.00
and on day 2:
yesterdays date, 1, 0.10
and on day 10:
today - 10 days, 10, 1.00
hth

Re: SQL: A few questions...
Posted: Tue Nov 17, 2009 1:08 pm
by carleihar
Alright, I think I got it now. Thanks so much!
Re: SQL: A few questions...
Posted: Tue Nov 17, 2009 6:02 pm
by califdon
Let me emphasize what iankent has already told you, making an even stronger statement. Relational databases are based on a mathematical model developed at IBM around 1970 by E. F. Codd, a mathematician, and there are strict rules about how data should be stored, in order for the relational calculus to produce mathematically valid results. In your 3 questions, you have violated 2 of the most fundamental of these rules! Data that can calculated from other data in the same table should not be stored. It should be calculated in the query that retrieves the data. So you never have a field for a date that is calculated as "today's date minus the date stored in another field". If a value can be calculated from other data in the same row of the same table, it should NOT be a field in the table. Next, every value stored in a column (field) should be "atomic", that is, single-valued. Although it is possible to store multiple values by using delimiters, as iankent illustrated, it is generally extremely bad practice. You will find it very awkward to do any searches on such a field, and again, it violates the rules upon which relational calculus is based. The way you resolve this is, as iankent said, to remove such a field to its own table, along with a foreign key to the original table. In that way, you can have as many items as needed associated with any row in the original table. This is the heart and soul of relational databases.
Re: SQL: A few questions...
Posted: Tue Nov 17, 2009 6:21 pm
by iankent
Well written califdon, much better than I could have explained it!! Just for a bit of extra info, this (now rather old) article explains the reasons behind refactoring a database to achieve normalization and to avoid data redundancy, and is one of the few articles I've found that explains it in plain English!
http://www.troubleshooters.com/littstip/ltnorm.html
Though its 14 years old, the same principles of database normalization still apply

It took me a while to get my head around it all, and gave me quite a few headaches along the way, but the time spent refactoring any database will pay for itself extremely quickly!
Re: SQL: A few questions...
Posted: Tue Nov 17, 2009 6:37 pm
by califdon
Thanks. You get better at it after preparing lesson plans for a decade or so.
That's a good reference page. You're right, things don't change much with respect to mathematical theory. It's too bad that so few people have any idea that a relational database is much different from a spreadsheet. It's really not intuitive at all.