Re: Easy normalization how-to?
I was hoping someone could improve on my suggestion. But, not seeing any further replies, I guess my solution must be optimal (troll, troll :) --John On Saturday 03 July 2004 03:34 am, John Hicks wrote: > On Friday 02 July 2004 11:40 pm, Your Name wrote: > > ... I now realize the importance of normalizing my > > data, > >... what [is] the easy way... of getting > > it done now that I already have a database set up. > > > > Suppose I have a table "books", consisting of > > "id", "title", and "publisher"; the "publisher" is > > redundant. I'd like to create a "publisher" table > > consisting of "id" and "publisher", and then I > > want my original "books" table to use its > > "publisher" column to hold the value of > > "publisher.id" instead of the publisher's name. > > Here's a 3-a.m.-I-really-shouldn't-be-doing-this-now > stab at it: > > Extract the publisher names from books into a new > publisher table: > > create table publisher > select distinct publisher from books; > > Add a primary key to the new publisher table: > > alter table publisher > add column id int primary key auto_increment; > > Use a join to create a new table with both publisher > and publisher id: > > create table newBooks > select books.*, publisher.id as publisherId > from books, publisher > where books.publisher = publisher.publisher; > > Drop the redundant column publisher: > > alter newBooks drop column publisher; > > rename table books to oldBooks; > rename table newBooks to books; > > There's bound to be better ways to do it. I'll check > back tomorrow to see what I can learn from others' > suggestions. > > Regards, > > John > > On Friday 02 July 2004 11:40 pm, Your Name wrote: > > I'm learning more about SQL all the time--I'm new > > to things now but I'm trying to read. I now > > realize the importance of normalizing my data, but > > what I can't figure out is what the easy way is of > > getting it done > > now that I already have a database set up. > > > > Suppose I have a table "books", consisting of > > "id", "title", and "publisher"; the "publisher" is > > redundant. I'd like to create a "publisher" table > > consisting of "id" and "publisher", and then I > > want my original "books" table to use its > > "publisher" column to hold the value of > > "publisher.id" instead of the publisher's name. > > > > Is there a way I can do this with an existing > > table full of data, other than going through it > > line-by-line with > > a scripting language? > > > > Thanks! > > > > Jen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy normalization how-to?
On Friday 02 July 2004 11:40 pm, Your Name wrote: > ... I now realize the importance of normalizing my > data, >... what [is] the easy way... of getting > it done now that I already have a database set up. > > Suppose I have a table "books", consisting of "id", > "title", and "publisher"; the "publisher" is > redundant. I'd like to create a "publisher" table > consisting of "id" and "publisher", and then I want > my original "books" table to use its "publisher" > column to hold the value of "publisher.id" instead > of the publisher's name. > Here's a 3-a.m.-I-really-shouldn't-be-doing-this-now stab at it: Extract the publisher names from books into a new publisher table: create table publisher select distinct publisher from books; Add a primary key to the new publisher table: alter table publisher add column id int primary key auto_increment; Use a join to create a new table with both publisher and publisher id: create table newBooks select books.*, publisher.id as publisherId from books, publisher where books.publisher = publisher.publisher; Drop the redundant column publisher: alter newBooks drop column publisher; rename table books to oldBooks; rename table newBooks to books; There's bound to be better ways to do it. I'll check back tomorrow to see what I can learn from others' suggestions. Regards, John On Friday 02 July 2004 11:40 pm, Your Name wrote: > I'm learning more about SQL all the time--I'm new to > things now but I'm trying to read. I now realize > the importance of normalizing my data, but what I > can't figure out is what the easy way is of getting > it done > now that I already have a database set up. > > Suppose I have a table "books", consisting of "id", > "title", and "publisher"; the "publisher" is > redundant. I'd like to create a "publisher" table > consisting of "id" and "publisher", and then I want > my original "books" table to use its "publisher" > column to hold the value of "publisher.id" instead > of the publisher's name. > > Is there a way I can do this with an existing table > full of data, other than going through it > line-by-line with > a scripting language? > > Thanks! > > Jen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Easy normalization how-to?
I'm learning more about SQL all the time--I'm new to things now but I'm trying to read. I now realize the importance of normalizing my data, but what I can't figure out is what the easy way is of getting it done now that I already have a database set up. Suppose I have a table "books", consisting of "id", "title", and "publisher"; the "publisher" is redundant. I'd like to create a "publisher" table consisting of "id" and "publisher", and then I want my original "books" table to use its "publisher" column to hold the value of "publisher.id" instead of the publisher's name. Is there a way I can do this with an existing table full of data, other than going through it line-by-line with a scripting language? Thanks! Jen __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
