hey tech geeks.
Here's what i'm trying to do.
i've been messing around with database design using something like creator.zoho.com because it's been fairly intuitive for simple stuff and has the ability to get into more depth if you feel like messing around with actual code.
i don't have a lot of database design chops. What i'm trying to do is this:
We're trying to streamline the way that attendance is taken for the marching band. What i'm trying to do is to make it so that all of the section leaders are responsible for submitting an attendance form every week to mark whether or not their section is present, absent, or partially absent. The records should be updated every week by the membership. The end result should be a large grid of "here" "absent" and "partially absent" data that i can then apply a basic formula to to have it calculate their final attendance grade.
A few details:
Ideally (but not necsesarily), i want to make it so that they can only update records for the week in question, thus making any other week read-only, although i can overwrite it as an admin.
The problem i'm running into with the zoho database is that i have a couple of different scenarios that i can create and neither are ideal. The first option is to create a table in which all of the attendance marks are a Y plane to the member name's X plane, as in:
- Week 1 M Week 1 W Week 1 F Week 2M Week 2 W Week 2 F etc... John Doe - - - - - - - Mary Jane - - - - - - - Janet Smith - - - - - - - The problem with that is that a) a student entering in data for a particular member has access to all of the attendance marks regardless of when (i can't lock down certain fields once they're written, at least i don't think so), and b) the form will end up being a huge grid for them to navigate which in zoho is not the most intuitive design.
The second option is to create a table that just uses one MWF column that is grouped by Weeks. So all they see is the week that they have available, and a new record gets created in the back end in more list format, as in:
- M W F Week 1 John Doe - - - Week 1 Mary Jane - - - Week 1 Janet Smith - - - Week 2 John Doe - - - Week 2 Mary Jane - - - Week 2 Janet Smith - - - etc. - - - The problem with that is that it makes it somewhat more difficult to see quickly where a student or a group of students is at, and it may make it more difficult for me to come up with a formula that can correctly calculate the grade for each student because it's dealing with more array-like things.
Granted, i can maybe keep a running store of absences/partial absences for each student in a hidden variable, but it still makes the visual look of it fussy for quick analysis.
any thoughts here? should i just give up and have them just send me the stuff in excel format and do it in excel because that's what i know what to do better?
profile
welcome to the
lifeofmendelyou can also find me here:
me
quicklist
Designed by chasethestars
chasethestars
Comments
1. List of members
2. List of dates
3. List of appearances (member, date of attendance)
Of course, you wouldn't get the fancy spreadsheet look, but that's what an HTML interface would be for. I'm just looking at it from a purely relational standpoint, so maybe that's not what you're looking for.
(I've been on kind of a DB binge lately with my secret project...)
The database only stores the data (perhaps doing consistency checks of various sorts.) It's up to your application to enforce constraints on who can edit what, and when.
Or at least, that's how it works for well-separated systems. Zoho looks to integrate a lot of things together. It's unclear how fine-grain the access they give you is.
For designing database tables, you should think more about how the data relates to itself rather than how others should see it. Seeing and aggregating it should happen at an application or reporting level. If you want any kind of control over how and when your users enter data, you shouldn't give them direct access to the table. I semi-agree with the previous post--it sounds like you do want to set access permissions at the application level here (although there are cases where you'd want to set permissions at the database, table, or column level).
The problem is that the process was broken on the step before that; they were all tasked with turning in paper attendance sheets to the drum major and then that drum major would give the sheets to me and then i would enter the data. Especially in the spring, we ended up having an issue where people didn't turn their stuff in on time and some sheets would go missing &c, so the reason why i want to do it online is to a) avoid going through too many channels to get the paper to me, b) not have to duplicate data entry, and c) have a way to make them accountable for giving the attendance data on time.
Because i want them to enter the data, that's why the UI matters. if it was just me, i wouldn't care how it looked, only how the data relates to each other; i can draw the conclusions myself and do whatever formula manipulation i need. In this case, i need to make the GUI simple and clear and then have the application be able to interpret how that stuff is put in to have it turn into relevent data on the backend.
----- M ----- W ----- F
user
user
user
user
user
and for that given week they enter in present, absent, partial. on the backend, the database would figure out by date or by week which week this data would belong to without the users having to worry about it. The next week, it looks exactly the same to them, but puts it in a different spot in the table because the week is now different.
That, and there's a distinction in my mind between a spreadsheet and a database, and the stuff i've been using it for prior is more database-y than spreadsheet-y. This *could* be more spreadsheet-y, but with the controls i'm trying to enforce it feels more database-y.