search

gilienv / EssOilDB

Restructuring of Essential Oil Database
Apache License 2.0
8 stars 6 forks source link

Suggestion for restructuring of EssoilDB Tables #33

Open manishkumarnipgr opened 5 years ago

manishkumarnipgr commented 5 years ago

  1. observationsdata oid ---- Primary Key(PF)---Auto increment(Observation id --to be separate for every field) pid---- Foreign Key(FK)----plantid from plantdata cid------FK----compound id from compounddata percent
    ppid-----FK------plant part id from plantpart timeofexpt biblioid-----FK-------bibliography id from bibliography locid--------FK-----location id from locationdata

  2. plantdata pid-----PK-----Auto increment pname--------- plant biological name psname-----plant synonym name pfid------FK--------plant family id from plantfamily pgid------FK--------plant group id from plantgroup profilecode --------to be fetched from info_plant.csv

  3. Compounddata cid------PK----Auto increment cname----------compound name csname------------compound synonym name includes iupac name cfid----FK------ compound family id from compoundfamily cagroup-----separated by '#' [FK group]--- compound activity id from compoundactivity smilescode imageofstructure--- (will store image name with format e.g. image1.jpg)

  4. bibliography biblioid------PK--- Auto increment doi authorname journalid-----FK--- journal id from journalname articletitle volumeinfo pageinfo

  5. Locationdata locid------PK---Auto increment locationofexpt countryid-----FK----country id from countrydata state city coordinates

The following tables are also needed to be created to avoid any duplication of data in fields in near future.....Plant part will be repeated any times in observationsdata. For e.g.

  1. leaf will be repeated every time and whenever new record has to be inserted it has to be manually entered leaf but if we have list already with us and leaf already exists in table then we don't need to enter it again... just fetch ppid from plantpart table and store it in observationsdata.

  2. if India is already with us what's point of entering it again. Just fetch its id and assign it in locationdata table.

  3. plantpart ppid-----PK----Auto increment ppname------plant part name

  4. plantfamily pfid-----PK-----Auto increment pfname ------- plant family

  5. plantgroup pgid----PK----Auto increment pgname--- plant group

  6. compoundfamily cfid------PK----Auto increment cfname---- compound family

  7. compoundactivity caid ------PK----Auto increment caname ----- compound activity e.g. anti-malaria

  8. journalname journalid------PK----Auto increment journalname ----- journal name

  9. countrydata countryid------PK-----Auto increment countryname----- country name

petermr commented 5 years ago

I comment inline...

observationsdata
oid ---- Primary Key(PF)---Auto increment(Observation id --to be separate for every field)
pid---- Foreign Key(FK)----plantid from plantdata
cid------FK----compound id from compounddata
percent
ppid-----FK------plant part id from plantpart
timeofexpt
biblioid-----FK-------bibliography id from bibliography
locid--------FK-----location id from locationdata

PMR: that is what was decided. The various id's will be repeated many times

plantdata
pid-----PK-----Auto increment
pname--------- plant biological name
psname-----plant synonym name
pfid------FK--------plant family id from plantfamily
pgid------FK--------plant group id from plantgroup

I think pfid and pgid can be separated - they are not part of the data. profilecode --------to be fetched from info_plant.csv

NO. If I understand what profilecode is it has to be in observationsdata, else there is no way of grouping
cid and percent. into separate subtables 

Compounddata
cid------PK----Auto increment
cname----------compound name
csname------------compound synonym name includes iupac name

NO. compound may have many synonyms. How are these managed cfid----FK------ compound family id from compoundfamily this is not part of the data so could be separated cagroup-----separated by '#' [FK group]--- compound activity id from compoundactivity this is not part of the data so could be separated smilescode imageofstructure--- (will store image name with format e.g. image1.jpg)

bibliography
biblioid------PK--- Auto increment
doi
authorname

there are many authors per single DOI journalid-----FK--- journal id from journalname articletitle volumeinfo pageinfo

Locationdata
locid------PK---Auto increment
locationofexpt
countryid-----FK----country id from countrydata
state
city
coordinates

The following tables are also needed to be created to avoid any duplication of data in fields in near future.....Plant part will be repeated any times in observationsdata. For e.g.

leaf will be repeated every time and whenever new record has to be inserted it has to be manually entered leaf but if we have list already with us and leaf already exists in table then we don't need to enter it again... just fetch ppid from plantpart table and store it in observationsdata.

I think I agree with this, but need to see formal example

if India is already with us what's point of entering it again. Just fetch its id and assign it in locationdata table.

I am confused. You must describe each table separately\

plantpart
ppid-----PK----Auto increment
ppname------plant part name

plantfamily
pfid-----PK-----Auto increment
pfname ------- plant family

plantgroup
pgid----PK----Auto increment
pgname--- plant group

compoundfamily
cfid------PK----Auto increment
cfname---- compound family

compoundactivity
caid ------PK----Auto increment
caname ----- compound activity e.g. anti-malaria

journalname
journalid------PK----Auto increment
journalname ----- journal name

countrydata
countryid------PK-----Auto increment
countryname----- country name