Schema design and improvement

lvergergsk / BibGallery-FrontEnd

0 stars 0 forks source link

Schema design and improvement #15

Open lvergergsk opened 6 years ago

lvergergsk commented 6 years ago

editor

Here is SQL file: dblpCreateTables-OracleDB.zip

A basic structure is created according to #6. schema design may need improvement due to complexity and availability of data.

lvergergsk commented 6 years ago

publication

SQL file is here: dblpCreateTables-OracleDB.zip

For updated ER diagram in #6.

@kenkangxgwe Please check SQL code to make sure this is what you mean.

kenkangxgwe commented 6 years ago

Where is the collect relationship table? Please see https://github.com/lvergergsk/BibGallery-FrontEnd/issues/6#issuecomment-369359451

lvergergsk commented 6 years ago

@kenkangxgwe According to this slide, 1:m relation does not need a separate table. Each attribute on this relation is can be moved to "m" side. (This makes performance better.)

kenkangxgwe commented 6 years ago

The reason why we need to separate the relationship table out from the publication table is that not all of the publication are "Article", "Inproceedings" or "Incollection", for those tuples are not the above three types, they do not have a corresponding collected relationship. This results in the sparse of the collect column. @lvergergsk

lvergergsk commented 6 years ago

@kenkangxgwe Sorry, I'm not persuaded, please allow me to give mine point.

The original is like this: COLLECTION - publication_id (sparse here.) -> PUBLICATION Your proposed schema is like this: COLLECTION - collection_id -> COLLECT - publication_id (sparse here.) ->PUBLICATION They both have a sparse connection.
We don't have a collect column, In your ER diagram, you say COLLECT table use publication_id as the primary key. So the publication_id is a foreign key and primary key of COLLECTION, this already established the relation, I don't see the point to add another table.

lvergergsk commented 6 years ago

coauthor