MySQL Forums
Forum List  »  Newbie

Re: Normalizing database
Posted by: Peter Brawley
Date: May 20, 2022 03:19PM

The logic is ,,,

1 create a detail table with a foreign key referencing the master table, eg ...

master(mid int unsigned primary key auto_increment, ... )
detail(
  did int unsigned primary key auto_increment, 
  thing varchar(...), 
  mid int unsigned, 
  foreign key(mid) references master(mid) on update cascade on delete cascade, 
  ...   -- OTHER POSSIBLE COLS
);


2 write a routine which walks master, for each row parsing that row's comma-separated thing list into individual things and for each of these ...

insert into detail set thing='thisthing',mid=master.mid, ...);


3 drop the master.thinglist column


This is much easier to do in a language that has strong string-handling capabilities, eg PHP or C#, but it can be accomplished, albeit awkwardly, in a MySQL stored procedure.

Options: ReplyQuote


Subject
Written By
Posted
May 20, 2022 12:50PM
Re: Normalizing database
May 20, 2022 03:19PM
June 04, 2022 10:47AM


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.