a newbie question on table design

Поиск
Список
Период
Сортировка
От fdu.xiaojf@gmail.com
Тема a newbie question on table design
Дата
Msg-id 47B58A3E.6000506@gmail.com
обсуждение исходный текст
Ответы Re: a newbie question on table design
Re: a newbie question on table design
Список pgsql-general
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,

В списке pgsql-general по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: Are indexes blown?
Следующее
От: "Shoaib Mir"
Дата:
Сообщение: Re: Are indexes blown?