Designing a database

[yaelago]

Hi everyone!

E$$ENTIAL MEDICINE$ was launched only two weeks ago, and has already received over 100 entries and verified information of over 20 FDA approved medicines! We also have some amazing participants planning the automation of data collection looking forward (see #2).

It's now time to start thinking about the design of an open database for sharing this information with the world. If you have experience working with databases, have specific needs or requirement from such a database, or just have ideas of how to design it in a clear and useful way, we would love to hear and discuss!

@liapaola @IngaTopolnicki @Ecaloota @narath @borawl @Propargyl

[borawl]

Database design and implementation is my background. I was thinking Sql Server. What do y'all think? And what about the front end? What/who is hosting the final application? Other things to consider:

• Will records be added/updated/deleted from the database?
• Will lookup tables be needed for searching and editing? Lookup tables are great for ensuring consistency. For example, a lookup table containing the names of US 50 states rather than a text field. A text field will result in different incarnations of the name of a state. Example, Virginia, VA, Va. If there are issues such as this in the current data file, we need to get that cleaned up. Also accounting for any missing data.

Depending on the size of the data file, I again recommend beginning with a smaller data set to work with to remove the kinks. I'm really concerned about scrubbing the data before moving to database creation.

Thoughts?

[yaelago]

Thanks @borawl , these are really importation points. The database will have to be quite dynamic as we will be adding additional data types in each of the project phases. Currently we have only been looking at FDA approved medicines in the US, but this will expand a. to other countries b. to additional data (e.g. medicine costs, chemical characteristics etc.). This information may be partial for some medicines. We will also be adding information to the database as the project receives input. But this will probably be manual and not automatic due to quality control issues.
I am happy to start with a small database to start and test things up, before moving on with the creation of a bigger one.