SQL Question

finnpark

Hello All,

Im hoping to do a MYSQL database with SQL and PERL.

I have created a table as follows:

ID Name Age

I am able to add to any specific column individually. However, is itpossible to have 1 ID with loads of different names associated with that ID ?

my $sth = $dbh->prepare("INSERT INTO tablename (Name, Age)
VALUES
('finnpark','100')");
$sth->execute;

The above code will just add to each column. However, I want to associate the name and age with 1 specific ID and none others. I want to have 1 ID with more than 1 name and more than 1 age and I dont want those names and ages to be connected in any way with other IDs.

Thanks for any help, my object orientated programming skills are also poor.

jmcc

Set up ID as an auto incrementing field in the schema. It is basically a unique number for every record that will increment automatically as you add data.

id(INT) NOT NULL AUTO_INCREMENT

Regards...jmcc

democrates

If you have two people named Michael Murphy of the same age, how do you tell them apart? An update based on name and age would change both records:eek:. You'd need to use the auto-increment id as jmcc advised, and 'where id=n' for some selects, and all updates and deletes, and also have some other information stored (eg email address) that allows the system and its users to tell one doppleganger from another. This is called maintaining data integrity.

Even at that human failure can lead to errors. I've had money lodged to my account that was supposed to be for my dads - the bank teller searched by account name instead of the unique account number on the lodgement slip!

If it's a workplace database there may be staff numbers available that you can use as the key of the person table. Social security number is a great option in theory, but not typically used on DIY systems given the risk to privacy or of identity theft. Certainly never for use on non-governmental public sites.

Many sites use 'username' or email address as the key of the person table where the public can register an account, since each username can only be used by one person, a username will never apply to more than one person. The weak point is that one person could have more than one username, using different emails to register for example. That may not be a problem. Boards.ie also logs ip addresses to counter subterfuge.

Depending on how important the system is, choose a key for each table accordingly, no point in using a sledgehammer to crack a nut after all. As a beginner I'm guessing this is a learning project so there's no data risk?

finnpark

Thanks for the replies, the auto increment is handy alright:)

Yes, I do intend having a separate ID for each person. This is a learning exercise but I would like to use a database on my web site instead of reading and writing to files. So I want to do things right for the beginning.

Can I ask: Is there anything wrong with having a load of tables? Im thinking of having a different table for each person with loads of "results". The results will be quite detailed with many different columns. Then I will have a table which just stores a list of the names/IDs which is just there to check whether or not a table exists for a certain student. I may have up to 50 tables, is this a bad idea?

stevenmu

finnpark wrote:

Can I ask: Is there anything wrong with having a load of tables? Im thinking of having a different table for each person with loads of "results". The results will be quite detailed with many different columns. Then I will have a table which just stores a list of the names/IDs which is just there to check whether or not a table exists for a certain student. I may have up to 50 tables, is this a bad idea?

Yes, bad idea. Having 50 tables in itself is probably ok, but not for this purpose. It would get messy if you add or remove students, or change the spelling of their names or something. It's much easier to store the results for all students in the same table and to have a field holding their id code, then when you want results for a particular student you can select the records from the results table using their id code in the where clause (i.e. "select * from results where studentid = 123")

edit: This also allows you to easily get summaries of results or calculate things like averages.

bonkey

finnpark wrote:

However, is itpossible to have 1 ID with loads of different names associated with that ID ?
...

Sounds to me like the first thing you need to do is read up on Relational Databases, and the principles of Normalisation.

I'm not trying to be unhelpful, but it really sounds like you don't understand the basics yet, and simply answering your question won't help that.

jc

finnpark

Thanks for the replies, both have been helpful.

Yes, I do need to do some more research ont he basics before diving in. What Im really trying to do is to store some information that will not increase in quantity (ID, name, Email etc) but also associated with each id will be results figures, results dates, results comments, results level etc which will be added to say on a weekly basis for the same id.

Thanks for your help, I may get back to you if I get stuck again:) . I will do some more research 1st.

Cheers,
finnpark

Zaph0d

Start here

democrates

PS you don't need to learn object oriented programming to build a small application, procedural will do just fine and keeps it simple.

After 22 years in IT I find I'm still using procedural, not just because of the performance overhead of OO, but because I've found OO over-complicates the learning and design process. My users have been delighted with what I've given them and I'm proud of my work. Look at all the Java questions on this thread, it has morphed into a monsterous nightmare for newbies.

If you find there's a piece of code you're using repeatedly, you can put it in a function and call that each time, and you can also put them in a seperate include file for use by various scripts, but even that's not absolutely necessary. My approach with a new system is usually to get something basic up and running fast and give it a good test-drive with real data (particularly important if other people will be using your system to get them to test it too).

That way you'll find you learn not just if it functions correctly and covers all the real world situations, but how easy your user interface design is to use. If you don't like it, change it, and then when you go to build the next part of the system you're in a better position to know what to produce. With experience you'll find your first attempts get closer to the mark and there's less effort involved.

If you try the alternative of learning all about relational database design and object oriented programming *first*, how long will it be before you have a functioning system? Make the mistakes, solve each problem in turn coming back here each time if necessary, you'll learn and produce results faster.

Zaph0d

You don't need to know anything about OO to use DBI for simple purposes. A balance of theory and experience helps with programming. If you are thinking of making a table for each item in your database this indicates that you will save time by spending an hour reading about the absolute basics of database design.

is_that_so

Learning DB design will help you look at your data properly and see what data logically goes together. As already indicated DBI is very easy to use, so time spent on the DB design, normalisation and SQL syntax for DML, DDL will be worth it. I also agree with the procedural approach but it might be a bit too far to stretch for you now. Good luck.

finnpark

Getting on ok now with database.

Say I want to read in the data, and then print out to screen in order of eldest first down to youngest.

Thway Im doing it at present is in alphabetical form. Im reading each column into an array and printing out each array indivually.

However, when I sort the age column the values in the age column don't match the other colums as they been sorted (mixed up location wise). How do I sort the ages while sorting the other columns to match up?

I used @age_array=sort {$a <=> $b} (@age_array); to sort the array in PERL but maybe there is way of sorting the array for ages while maintaining corresponding data in the other columns.

Thanks for any help:)

jmcc

finnpark wrote:

Getting on ok now with database.

Say I want to read in the data, and then print out to screen in order of eldest first down to youngest.

Look up ORDER BY in the MySQL manual section dealing with the SELECT statement. It does exactly what you want to do.

http://dev.mysql.com/doc/refman/5.0/en/select.html

Also there is a good tutorial on SQL (Oracle) by Philip Greenspun:

http://philip.greenspun.com/sql/

It is well written and covers almost everything. It might be a bit heavy going if you are just playing around with databases but it is a lot easier to understand the concepts by reading it than it is to read the academic books on database theory. (It is a toss up between those and the work of Arthur C. Clarke on which can send someone to sleep quicker. )

Though for MySQL this is the most useful link:
http://dev.mysql.com/doc/

Also this link has piles of FAQs and tutorials:
http://directory.google.com/Top/Computers/Programming/Languages/SQL/FAQs,_Help,_and_Tutorials/

The most important thing about database work is to sit down and work out what you want to do and then go about doing it. It doesn't matter if you are building a db with hundreds of GBs of records or a db with 50 records.

Regards...jmcc

democrates

is_that_so wrote:

Learning DB design will help you look at your data properly and see what data logically goes together. As already indicated DBI is very easy to use, so time spent on the DB design, normalisation and SQL syntax for DML, DDL will be worth it.

Agreed, yet I guarantee it'll be quicker to present the requirements here and get advice (from those willing to give it) than taking off down academic roads. Call me gonzalez.

is_that_so wrote:

I also agree with the procedural approach but it might be a bit too far to stretch for you now.

It's easier to learn procedural than OO. OO came from procedural, and took it to the extreme, with costs in performance, complexity, and ironically, practical flexibility. As I said, you can put frequently used code in a function (aka procedure), but that's not even necessary for a beginner - who can code their entire applications conditional operation with IF statements in one script.

The benefit of oo is supposed to be for the programmer first, and thereby the user. Code re-use, encapsulatiuon, inheritance, sounds great, all hail. But in practice, class definition to live up to its ultimate design goal implies industry standard ontologies. So worldwide, 'Invoice' would have a single definition. OO has not delivered that, neither did procedural programming or the RDBMS community in fairness. Only the W3C have triggered this, fuelled by the Internet boom.

Sadly, the W3C are coming at the data management question from a document perspective. HTML is a simplified version of SGML. XML expands it. Namespaces = ontologies. Success at last? No. Its a hierarchichal paradigm. Theoretically this is defended as a means of keeping Internet content easily understandable to all, but its about as efficient running as a pig with a wooden leg, and worse, a hierarchical representation of data is just one view of the relational reality. It's a lock-down.

But no worries, when oasis succeeds in establishing ebXML, and all the others have their namespaces adequately specified, the inefficient hierarchical xml paradigm will yield to the relational as happened in the database world before, because at least the W3C followers will have agreed on datatypes and ranges for individual fields. It's a battle won, but now to be re-fought in another arena thanks to the .com irrational exhuberence powered w3c, charging through the data management world with their sgml blinkers on.

Again, to the OP, get coding, test, ask questions here, and learn fast. Don't be put off by people saying 'go and learn x first'. Much more fun to code and use it, saving time and getting better results.

finnpark

Thanks all for all your help so far.

I don't really like all that ER Model etc stuff to be honest. I would rather dive straight in to be honest even if I end up making a botch job of it.

SQL is a nice language, very effective for a lot of database related stuff.

I might use dates, times etc in my database. Ive read something on the web on timestamps. Any advice in relation to use of timestamps , storing/querying dates/times etc are welcome.

Thanks,
finn:)

democrates

A timestamp is ideal for recording when a record was inserted, but that's it. It's going to put the current date/time in according to the server clock.

In practice you might enter records on a friday for what happened that week, in which case you want to be able to insert dates of your choosing. I wouldn't bother with the time unless necessary, and it might be, if it's the case that you are storing exams v results, and each exam has a start time for example.

What tables have we so far if I understand this right, person, result, and exam? If so, here's a first cut (key fields in bold):

Person table:
personid (auto-increment integer)
forename (character 32)
lastname (character 32)
dateofbirth (date)

exam table
examid (auto-increment integer)
subject (character 32)
level (not sure what this is, see below)
start (datetime)

result table
examid (integer)
personid (integer)
result (say integer if it's going to be a percentage?)
comments (char or text? depending on the size required)

Note that the examid and personid on the result table are not auto-increment, just integer, because they're coming from other tables (therefore called foreign keys).

If 'level' is something about each result it should be in that table, but if it's about each exam (EG 'Higher' rather than 'Ordinary') as I've guessed above, it should be in the exam table, rather than repeating it for every record in the result table and wasting space.

How does that look? Will your facility provide some balance to school benchmarking?

In addition to the links jmcc has provided, two key references to use are mysql and perl.

bonkey

finnpark wrote:

I might use dates, times etc in my database. Ive read something on the web on timestamps. Any advice in relation to use of timestamps , storing/querying dates/times etc are welcome.

The only advice I could give in this regard is to agrew with the direction you're apparently heading:

1) If you need date/time information, store it in date/time fields. i.e. Don't store it in numeric or character fields, no matter how "simpler" it may appear initially.

2) If you only need date or time, then only store date or time. In other words, if you only need a date, don't store the hh:mm:ss alongside it.

3) If working with date-and-time, remember that something like the following can cause problems:

SELECT * FROM MyTable where SomeDateTimeField <= '31-12-2005'

Why can it cause problems? Because '31-12-2005' (a date without a time) will default to *midnight* of that day. Thats the start of the day in question. So even though I've asked for everything with a data less than or equal to the last day of 2005, what I'll actually get back is anything up to midnight (where Dec 30 turns to Dec 31). Anything on the 31st itself will not be included.


jc