Designing a database for R&D purpose

[yanglive7059]

Dear folks,

recently I'm exploring a database for R&D purpose use. Previously we were using Excel, but as time grows, the file size grows larger which I find it risky in terms of efficiency and data safety.

I'd like to link all the information of several projects, where the table would look like this:

Table 1
Project_List
- Project code


- Project name

Table 2
Trial_List
- Project code
- Project name
- Trial # (Unique ID combining Project code + the number of trials, Eg., ABC-1, where ABC is the project code and 1 is the 1st trial)

Table 3
Option_List
- Trial #
- Trial Options
- Machinery Settings

Table 4
Option_Results
- Trial #
- Trial Options
- Results A
- Results B
....
- Results Z

Would these something viable for my purpose? and is there any better options? I would like to learn on the relationship between the tables and the best form for these data...

[CJ_London]

just use autonumber for unique ID (it is called a PK - Primary Key) and to link to child tables, the child table has a FK (Foreign or Family Key). PK's only function is to uniquely identify a record and should carry no other meaning as you are doing with Trial #.

avoid using spaces and non alphanumeric characters in names - they will come back to bite you in the future

and data is stored vertically in databases, not horizontally as in Excel.

With a very few exceptions (this doesn't appear to be one of them) you store data only once (e.g. project name does not need to be repeated in table2)

Not enough detail to suggest what is right or wrong but I would be starting with a design along these lines

tblProjects
ProjectPK
ProjectName
and perhaps start and end date fields

tblOptions
OptionPK
OptionName

tblTrials
TrialPK
ProjectFK
MachinerySettings (these may need to be in a separate table as per tblTrialOptions below if there is more than one setting)
and perhaps start and end date fields

tblTrialOptions
TrialOptionPK
TrialFK
OptionFK

tblOptionResults
OptionResultPK
TrialFK
OptionFK
Result (these may need to be in a separate table as per tblTrialOptions above if there is more than one result per option)


recommend read up on 'database normalisation', Moving from Excel thinking to database thinking can be quite challenging

[orange]

I agree with CJ's advice. I would also suggest the you write a description of a typical trial or a few trials to help users and you how the pieces fit together in your "business". Before getting into real physical database, you could create a data model of your tables, and, by using some sample "trials", you can adjust as necessary to match your needs. Most issues with database can be traced back to design, so don't overlook the importance (and ease) of "vetting your data model". The analogy here is like building a house --much easier to adjust the design at the blueprint stage than after the house is physically built. See this for info.

Good luck with your project.