Log in

prev | next

database design help

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 MWeek 1 WWeek 1 FWeek 2MWeek 2 WWeek 2 Fetc...
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:

Week 1 John Doe---
Week 1 Mary Jane---
Week 1 Janet Smith---
Week 2 John Doe---
Week 2 Mary Jane---
Week 2 Janet Smith---

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?

tag cloud:


( read spoken (13) — speak )
May. 7th, 2009 09:33 pm (UTC)
I don't know what Zoho is, but the best way to do that is with three tables:
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...)
May. 8th, 2009 07:12 am (UTC)
yeah, the relationships between the tables is something i understand, but it's the frontend that the users have access to that i'm running into challenges with. see some of my comments below which are a little jarbled because i'm tired.
May. 7th, 2009 10:51 pm (UTC)
Taren's answer hints at a key point, which isn't quite fully spelled out.

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.
May. 8th, 2009 07:03 am (UTC)
right, and that's the challenge i'm coming up with. The creation of an application in zoho *can* do that, but at what sort of level i'm not sure. i did use it to give the kids access to a questionnaire that they answered but couldn't see the answers to, but that was easy because for the end user they just had to answer questions and then hit submit. i want more constraints on this one, but i also don't want it to be clunky.
May. 8th, 2009 01:50 am (UTC)
Your data appears to be pretty 2-D, so I'm thinking a spreadsheet might be your best bet in this case (especially because you're most comfortable with it). Have you tried pivot tables? They have a pretty convenient way of showing data aggregated by [condition1] and [condition2] (e.g. Show me attendance by student and date). I think you could do this with two data columns: name and attendance date. Simply don't enter a record if the person didn't attend that day. (I'm going to email you an example of what I mean, in case I'm not communicating it well.) Of course your data columns will get very long, but looking at the data will be easy sneezy, and it will eliminate that ever-adding-columns problem.

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).
May. 8th, 2009 07:11 am (UTC)
i used an excel spreadsheet this past year and it worked fine for me because i was the only one that had to enter the data, and i had all of the fun formulas and conditions &c.

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.
May. 8th, 2009 07:15 am (UTC)
oh, and the information is 2-D in reality, but the way i wanted it to be to the section leaders was more 3d, as in what *they* would see is:

----- M ----- W ----- F

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.
May. 9th, 2009 02:25 pm (UTC)
So how are the students accessing this? Sounds like your front end needs validators to enforce your conditions.
May. 12th, 2009 01:27 pm (UTC)
right. that's the problem that i'm trying to overcome. i need them to submit, but i need their ability to submit to be limited.
May. 9th, 2009 08:54 pm (UTC)
I looked briefly, and I'd probably give the "Attendance" view write, but not edit permission to the form, and share that one with section leaders, then give the "Master Attendance" view (which is just a copy of Attendance) edit permissions, and don't share that one with other people. As far as limiting where in the calendar people can apply attendance changes, I'm not sure, I only tooled around for about 30 minutes.
May. 12th, 2009 01:28 pm (UTC)
yeah, that's kind of what i'm coming to as a conclusion, either that or think a little simpler, as in not try to hold the master attendance sheet in zoho and do more manual work on my end via an external spreadsheet.
May. 11th, 2009 03:49 pm (UTC)
You might want to try using Google Docs. They now have an item called "form" that lets you submit answers to the form and it can report the data back in a summary or a spreadsheet. It might do what you need. Then you just need to pass out the form.
May. 12th, 2009 01:30 pm (UTC)
the biggest problem i've had with google docs overall is volume. As in, once when i was using it, i ran out of rows after only maybe... 500 rows? i forget exactly, but one of the reasons i decided to switch over to zoho was because i knew for certain things iw as going to be dealing with larger volume than google docs would permit.

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.
( read spoken (13) — speak )


welcome to the lifeofmendel

you can also find me here:

meSubscribe to me on YouTube


March 2017