Hi all, I have a large sdf file with many records of molecules and associated data items and I want to save them in a PostgreSQL database. There are about less than 40 data items for every molecule(the names of the data items fore each molecule are the same, but values differ). The number of molecules may exceed 20 million. Now I have come up with two ways to construct the table: 1) a table with about 40 columns, every data item has its corresponding column, and one molecule corresponds to one row in the table. This is direct and simple. The drawbacks is if I want to add more data types to the database, I have to modify the structure of the table. 2) a table with just 3 columns: CREATE TABLE mytable( id serial, data_name text, data_value text ); Then a single molecule will corresonds to about 40 rows in the database. If I need to add more data types to the table, I just need to add new rows with new "data_name" column values. The drawback of this table is it has too many rows(40 times of the former one) and waste a lot space. Which one is better, or there are some other smart ways ? I have another question. Since the data I need to save is huge, is it appropriate that I save the data value in compressed format ? Regards, ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
