database and primary keys

[Raoul B.]

Hi all,
I'm doing some experiment with postgresql so I installed the
application using it as a rdbms.
The generated schema doesn't contain primary key definitions, I think
that would be useful to add them directly to the db schema, btw it's
not a big issue.
Creating composity primary keys for instance on table
matures_seed_families I get duplicated value:
54014;21654;2
57154;12639;2

is it consistent with the data ? Or Is it my mistake ?

Best regards

[Anders Jacobsen]

Hi Raoul,

You are right that there are no explicit primary key definitions right now, primarily because miRMaid is "read-only" at the moment and this was therefore not a major concern for the first iteration.

However, there should be indexes for most important joins and look ups right now. It would be great though, to convert the appropriate indexes to primary key definitions. Maybe you work could could end up with a new version of the "001_mirbase_to_mirmaid.rake" script in db/migrate?

In the case of matures_seed_families, it is supposed to be a "transparent" many-many relation between matures and seed_families. A composite primary key is therefore not needed here, I think. Instead we have isolated indexes on the 'mature_id' and 'seed_family_id' columns to optimize joins from the matures and seed_families tables.

In the situation you are referring to

57154;12639;2

mature with ID=57154 (http://current.mirmaid.org/matures/57154) actually has a quite "special" matures sequence (AAAAAAAAAAACUCCCCCCC) that results in the 6mer (base 2-7) and offset 6mer (3-8) being identical, but we store both as entries in mature_seed_families. This is actually data redundancy and is something that should be corrected. I have created an issue for this on GitHub: http://github.com/andersjacobsen/mirmaid/issues

-Anders

--
You received this message because you are subscribed to the Google Groups "MirMaid" group.
To post to this group, send email to mir...@googlegroups.com.
To unsubscribe from this group, send email to mirmaid+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mirmaid?hl=en.