What is the easiest method of writing a small membership database, but with a many-many relationship?

Question

I want to write a small app for my Mum who runs a society. The society has members, and runs events (coach trips, garden visits, concerts, etc.). The members pay an annual subscription, and an amount for each event they attend. There are only about 100 members, and less than 20 events per year.

I imagined a screen for each member where the user could edit their details (or add new members), with a list of all the events coming up, so the user could write in whether the member was attending, how many guests they were bringing, and how much they had paid.

Plus a screen for each event, with a list of the members attending, how many guests they were bringing, and how much they had paid.

I am familiar with Sql, and C#, so, after an abortive foray into Microsoft Access I tried using the net 3.5 wizards to generate the necessary code. However, I am struggling to get my head around Microsoft's .net 3.5 data handling code, and I didn't get any answers to my question on the specifics.

So now I'm asking a more general question, as I figure there must be some kind of easy-to-use database framework, file manager, or whatever that could cope with this kind of many-to-many relationship.

Please let me clarify - I am comfortable with many-to-many relationships, and how to do them in SQL. I am also familiar with SQLite. What I am looking for is an easy way to present the data to the user (and allow them to update it) without spending more than a day or so coding.

Sigh! Apparently there is no easy-to-use GUI framework that will deal with this kind of problem. A market opportunity for me, perhaps?

Answer 1

I gave up in the end, abandoned all the wizards and the accompanying framework, and wrote the necessary code by hand.

Answer 2

I am a python adept so I would recommend you django.

Maybe you are not interested in doing a web application, but if you do, django provides many of the features you are probably intrested:

• It uses simple data models that allows you to do many-to-many relationships with 1 line of code;
• Automatic back-end generation, so the user can add data to the database using simple forms;
• Easy templating, if you wish to create some sort of front-end for members.
• Really good and active community.

Answer 3

For your many-to-many relationships use a separate table so something to track members and which events they've attended and paid for would have a foreign key that holds the member, another that holds the event, and together those would form the key for the table then just two booleans one for if they actually attended, another for if they paid (create the row when they indicate to the software that they plan to attend).

To be clear, you'd have a create table that looks something like:

CREATE TABLE attending (
   member INT FOREIGN KEY REFERENCES members(id),
   event INT FOREIGN KEY REFERENCES events(id),
   attended BOOLEAN NOT NULL,
   paid BOOLEAN NOT NULL,
   PRIMARY KEY(member, event)
)

NOTE: I might have messed up the syntax here, it's been a while since I wrote any SQL.

Obviously I'm assuming you aren't using an ORM or something that handles this sort of thing for you, so really I doubt this answers the question you really wanted to ask.

Answer 4

Take a look at SQLite

It has plenty of freely downloadable GUI admin tools for setting up databases graphically, and you can use the System.Data.SQLite ADO wrapper for accessing it..