General question on tables

[troutstudio]

I am writing a database to gather information on student's degree pathways. I have 4 years of intake; 2011 - 2014; the discipline  - maths, arts etc - 7 choices; then the subjects they have completed or are completing. In total this is 900 fields. This tool is not for official records; it will be used to calculate which subjects certain students still need to complete. So record numbers will not be large. My question is this: should I break up the table into years? This is the way I am planning to design the file: a student will login in via IWP (moving to 14 eventually) then select a year and a discipline - this will take them to the appropriate layout, to enter their data. I do however want a lecturer to be able to find a student by their student ID (this is entered by the student) so all tables need to be relational. What would you do? Is it work breaking up the table into years of about 200 fields? Will this make the design more efficient or am I making work for myself? Thanks for your time - it's much appreciated.

[eos]

I am writing a database to gather information on student's degree pathways. I have 4 years of intake; 2011 - 2014; the discipline  - maths, arts etc - 7 choices; then the subjects they have completed or are completing. In total this is 900 fields

This sounds very much like a sub-optimal (non-normalized) structure.

So record numbers will not be large.

They won't be if you have that many fields – but more records with fewer fields can much easier be summarized and analyzed than many fields in one record.

Is it work breaking up the table into years of about 200 fields? Will this make the design more efficient or am I making work for myself?

Did you mean “work” or “worth”? 

If you want to “more” normalize your solution, I suggest you go all the way, with a structure similar to:

Student --< StudentInTopicExam (id_student, id_topicExam, score) >-- TopicExam (id_topic, year, date) >-- Topic

More work initially, but much easier to handle down the road.

[troutstudio]

Thanks very much. I'm a novice  - do you mean that a field for a subject like SCDE100 in 2011 and 2012 are not created separately? Each year has a list of subjects that have some new codes and some old codes. I'm not gathering results. I see what you mean about optimising the data but I need to get my head around it. I have access to Lynda.com so I do have resources if I know what to look for. Would the key concept here be - displaying data using formulas? Thanks again.

[eos]

Would the key concept here be - displaying data using formulas?

The key concept is the same as always: identify your entities and create relationships between them. Ideally, every piece of data is stored only once.

The better defined your entities, the more naturally the relationships will appear.

A subject with code SCDE100 would be one record for 2011, another record (in the same Subject table) for 2012. These records (and the other records in that table) will have related (child) records in (I assume) an Exam table. These records in turn will beget join table records with a Student table etc.

Depending on the required granularity, you may also have a structure like

Subject (id, name) --< Course (id, id_subject, code) --< CourseInYear (id_course, year) --< CourseExam (id_courseInYear, date) --< StudentInExam (id_courseExam, id_student, score) >-- Student (id, name, etc.)

which allows you to store records like

Math --< Math|SCDE100 --< SCDE100|2011 --< Exam1 for SCDE100|2011 --< Stevie Ray in Exam1 for SCDE100|2011 >-- Stevie Ray 

or

History --< History|ABCD999 --< ABCD999|2014 --< Exam5 for ABCD999|2014 --< Mary Littlelamb in Exam5 for ABCD999|2014 >-- Mary Littlelamb

where every piece of data is stored only once, and related to other pieces. 

 

If Lynda offers learning resources re normalization and relational database design, I suggest you use them.

[troutstudio]

Thanks for this. I have completely redesigned the project. I have cut the fields down to less than 40. I do have another question please. I want to use a calculation to go to a layout. I've found examples but they are not right for this. I need a calculation to get the results of two fields, combine them and go to a layout. As in: 2011 and maths = go to layout maths2011. So I need to use 'get' and 'case' - am I on the right track? Thanks for your help.

[comment]

am I on the right track?

If you have a layout named "maths2011" as in year 2011, then no - you're definitely not on the right track. Years come and go, but the layouts in your solution should be permanent. The same thing applies to subjects: adding a subject is something a user should be able to do, without requiring the developer to modify the schema.

What you probably want to do is find the records where Subject = "Math" and Year = 2011.

Edited July 4, 2015 by comment

[bruceR]

"I have cut the fields down to less than 40."

But how many TABLES do you have? And how do the tables relate?

See attached, which begins to illustrate some points made by EOS and Comment.

Exam Results.fp7.zip

Edited July 4, 2015 by BruceR

[troutstudio]

If you have a layout named "maths2011" as in year 2011, then no - you're definitely not on the right track. Years come and go, but the layouts in your solution should be permanent. The same thing applies to subjects: adding a subject is something a user should be able to do, without requiring the developer to modify the schema.

What you probably want to do is find the records where Subject = "Math" and Year = 2011.

 

Actually, this is to create records, not find them. I want this scenario: a user chooses their year of enrolment and their subject area from two different pull down menus. Then they click on a "new record" button. This fires a script which takes these two pieces of data; and from them, switches to a layout which only has the subjects that were available in that year. The student ticks boxes for the subjects in that year as completed; or not. Then they print the result and take it to a meeting. This is for postgrads who are getting further degrees and an advisor needs to just look at a printout and see which subjects were actually finished. There are no results, as these have no bearing. Of course the database will need to be updated once a year, because they create some new subjects every year. I don't want to show Maths students who enrolled in 2011, the Humanities units from 2015 - so they need to be on a different layout. I have all the subject units as fields. There are about 25. Then there are 6 choices for year - they are values; and 5 disciplines which are values too. So it's now quite a simple database. I feel I only need one table. I realise for a developer that this will not be a production design but it's just something I am doing to assist mentors in advising students on missing units of study needed for a postgrad degree.

[comment]

It is very difficult for me to follow your description without having at least a "map" of your solution, such as an ERD.

Offhand, I would say that a student could select a subject and a year using global fields, then either view the corresponding subjects in a portal from their own record, or find them in a layout of the Subjects table.

There is no such thing as "a layout which only has the subjects that were available in that year"¹. Any layout is capable of showing any or all records of the table that the layout is associated with; it's only a matter of performing a find (or creating a found set in another way). If you're adding a layout for each possible found set, you're simply wasting your time and your solution's resources.

--
(1) That is, unless you mean a layout that only shows some fields of the underlying table (which I suspect you do). See my next note.

 

Of course the database will need to be updated once a year, because they create some new subjects every year.

Updated (with new data) - yes. Modified (with new schema) - no. That's not a smart way to proceed.

 

I have all the subject units as fields. There are about 25. Then there are 6 choices for year - they are values; and 5 disciplines which are values too. So it's now quite a simple database. I feel I only need one table.

IMHO, you should have an individual record for each atomic value. IOW, if there are 6 subjects offered each year, and so far there were 5 years, there would have to be a table with 30 records.

Edited July 4, 2015 by comment

[troutstudio]

It is very difficult for me to follow your description without having at least a "map" of your solution, such as an ERD.

Offhand, I would say that a student could select a subject and a year using global fields, then either view the corresponding subjects in a portal from their own record, or find them in a layout of the Subjects table.

There is no such thing as "a layout which only has the subjects that were available in that year"¹. Any layout is capable of showing any or all records of the table that the layout is associated with; it's only a matter of performing a find (or creating a found set in another way). If you're adding a layout for each possible found set, you're simply wasting your time and your solution's resources.

--
(1) That is, unless you mean a layout that only shows some fields of the underlying table (which I suspect you do). See my next note.

 

Updated (with new data) - yes. Modified (with new schema) - no. That's not a smart way to proceed.

 

IMHO, you should have an individual record for each atomic value. IOW, if there are 6 subjects offered each year, and so far there were 5 years, there would have to be a table with 30 records.

That last comment is especially useful, thank you. On the layout - I understand what layouts are capable of. I mean that I am using layouts as a way of presenting data choices. As for updating the schema - unavoidable in this environment. I am the person who shows admin the value of Filemaker. They would not pay for the license without me. If the project grows, I recommend they hire a developer. It's really as simple as that.

[eos]

As for updating the schema - unavoidable in this environment. I am the person who shows admin the value of Filemaker. They would not pay for the license without me. If the project grows, I recommend they hire a developer. It's really as simple as that.

I'm not sure what the first sentence has to do with the rest.

In a proper setup, you change the schema only when you have new entities and/or new relationships, or need new functionality that requires new utility tables or fields.

Apart from that, users simply add new data to the existing framework.

[bruceR]

"As for updating the schema - unavoidable in this environment."

Well; of course it is avoidable; and as a worker in an education environment you asked for some education about the best way to do things; and you got straightforward answers and an example file as well. 

[LaRetta]

Most of us who answer on these forums are professional developers who have been for decades.  There are few problems or structures we have not seen.  We've designed in hundreds of configurations, business situations and environments and there is little which surprises us (although it is still possible).  Our recommendations come from vast experience in this arena.

As for updating the schema - unavoidable in this environment. 

Management is lucky to have you on staff; no need to necessarily hire a developer but you will need to adjust to the world of database management and particularly FileMaker.  With the right structure, as is being recommended, your job will get much easier.  Truly.  And if willing to flex to this world, it will also become a great deal of fun. :-)

You might enjoy this thread:

Does anyone take us seriously?

Some of the greatest developers in this business started out as employees in other capacities, being handed a project to 'make a database' similar to your situation.  I commend you for sticking it out and asking the difficult questions.