MySQL Forums
Forum List  »  Newbie

Re: MYSQL Table Creation from exiting tables
Posted by: Phillip Ward
Date: February 05, 2015 06:36AM

Quote

id ------- PRIMARY KEY
name
gender
photo
OK. These are all fine, as far as they go.

Quote

password
Never, never store passwords in plain text in your database.
Always perform (at least) a one-way hashing on whatever the user enters, store the result of that and, when the User logs in, repeat this hashing process and only ever compare the hashed values.

OK, in a class you can get away with just adding a "salt" into the entered value; it's better than nothing at all and demonstrates that you have some grasp of Information Security.

Quote

age
Never store a value that you should derive from something else.
OK, you could store "age" but then you'd need a "batch" program to run every single night (without fail) that adds "1 day" to the value held in each and every record. Not a problem if you only have 5000 records; more of a challenge with 5000 million. YMMV.
And, of course, you have to decide whether or not to run this incrementing process for those Poor Souls who have "shuffled off this mortal coil".
Short answer; don't bother ...

Store "Date of Birth" (in a proper Date field, please) and calculate "Age" as and when you need it. (A View built on top of the Students table would be a really simple way to do this).



OK, let's get back to your actual question ...

Quote

address

I need to bring all the above mentioned addr fields in students table as students.address
No, you don't.
Again, this is derived data - the addresses "live" in the addresses table, you don't want to copy that data into another table, because it will get out of date (the source table gets updated but your copy won't).

What you want to do is to "link" an address, which contains a number of fields, to a particular student. Hopefully your addresses table has a Primary Key on it? Since a student can only reside at one address at a time (we'll ignore any historical record for now) then all you need to do is to add the Primary Key of the address record into the student record. Also, since the student has to live somewhere, you can add a Foreign key between the two tables to make sure that no student record can exist without a valid address record.

create table addresses 
( geo_id int auto_increment not null  
, streetnum ... 
, streetaddress ... 
, cityname ... 
, statecode ... 
, zipcode ... 
, primary key ( geo_id ) 
); 

create table students 
( id int auto_increment not null 
, name ... 
, password_hash ... 
, gender ... 
, date_of_birth date not null 
, address_id int not null 
, photo 
, timestamp 
, foreign key address_id references addresses ( geo_id ) 
);

Then, to get back student and address data in one, join the two tables together in your select:

select 
       s.id 
,      s.name 
,      a.streetnum 
,      a.streetaddress 
,      a.cityname 
,      a.statecode 
,      a.zipcode 
from   
           students  s 
inner join addresses  a 
      on   s.address_id = a.geo_id 
where 
       s.id = 1234

Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
Re: MYSQL Table Creation from exiting tables
February 05, 2015 06:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.