normalization steps

hi all: I am running informix 7.22 on solaris 2.6. I am currently transfering files from mailframe to bulid my datawarehouse. The mainframe current data are not normalized. I want to check with you guys with the steps of normalizing this database, see if I missed something or doing things stupid. let's call the initial data file MF. to simplify the matter, let's say only one index table will be created, and it consist of three fields from MF. 1. create the index table, with a unique index field (serial) and three fields I identified as the key. 2. extract unique values of the three key fields from MF and update the index field. 3. create a flat table MF1 and substitue the three key fields with the key value from the index table. 4. create the data table, load the flat file. 5. identify the key in the data table as a foreign key. Am I missing something here? it looks a little clumsy to me, are there better ways? thanks a lot. yan

Are you sure you want to normalize a Data Warehouse ????? Yan Zhu wrote: > hi all: > > I am running informix 7.22 on solaris 2.6. > I am currently transfering files from mailframe to bulid my > datawarehouse. > The mainframe current data are not normalized. > I want to check with you guys with the steps of normalizing this > database, see if I missed something or doing things stupid. > > let's call the initial data file MF. to simplify the matter, let's > say only one index table will be created, and it consist of three fields > from MF. > > 1. create the index table, with a unique index field (serial) and > three fields I identified as the key. > 2. extract unique values of the three key fields from MF and update > the index field. > 3. create a flat table MF1 and substitue the three key fields with > the key value from the index table. > 4. create the data table, load the flat file. > 5. identify the key in the data table as a foreign key. > > Am I missing something here? it looks a little clumsy to me, are > there better ways? > thanks a lot. > yan

Yan Zhu wrote: > > hi all: > > I am running informix 7.22 on solaris 2.6. > I am currently transfering files from mailframe to bulid my > datawarehouse. > The mainframe current data are not normalized. > I want to check with you guys with the steps of normalizing this > database, see if I missed something or doing things stupid. > > let's call the initial data file MF. to simplify the matter, let's > say only one index table will be created, and it consist of three fields > from MF. > > 1. create the index table, with a unique index field (serial) and > three fields I identified as the key. > 2. extract unique values of the three key fields from MF and update > the index field. > 3. create a flat table MF1 and substitue the three key fields with > the key value from the index table. > 4. create the data table, load the flat file. > 5. identify the key in the data table as a foreign key. > > Am I missing something here? it looks a little clumsy to me, are > there better ways? > thanks a lot. > yan If you create a table with the columns in your "MF" file plus a serial column you can then load the MF file. You will need to have a zero in the serial column position. If you do that, your serial numbers will be generated by Informix. The other indexed columns will take care of themsleves. If oyu have alot to load you may want to turn the indexes off during load to speed things up. If you cannot mess around with the MF file before it loads, you will have to have a staging file to hold it, from which you do an INSERT ... SELECT. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 -- If all else fails, read the instructions and the release notes. Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/