First some DB/SQL help, then some front end help.

[Kuo]

I'm a bit new to this and am looking for a starting point. I am doing this for a video game I play, but more than that, I'm doing it to learn something for myself. I've been wanting to learn this stuff for a while, but never had a purpose - but now that I have something to actually use it for, I want to figure it out.

So to give a bit of an orginzational view. We have events. We have people who show up to events. We have people who get items during events. We also have people who LOSE items during events (they're temporarily items).

In terms of how the information is inputted, I'm looking at:
** For event & attendence **
- Admin creates an event
- Admin creates new Person if needed
- Admin checks off who attended the event (from the Person list)

** For item management, linked to event & person **
- Admin can click on an event, and chose to "add item drop" -- From here, the admin can select from a list of items (check them off) if they dropped. If he checks them off, he can then select from a drop down menu who obtained the item (so the drop down menu will be populated from the Person list).
- Similarly, the Admin can UNCHECK an item, meaning that the item is no longer valid (it was a temporarily item and has gone away)
- From the other direction, admin can click on a PERSON and choose to "add item drop." From here, the admin can pick from a list of items (check them off). If checked, he can select from a drop down menu which event the obtain was obtained from (so the drop down menu will be populated from the Events list).
- Similarly, the admin can UNCHECK an item (same as above)

- Haven't decided on the item list yet. It can either be populated and locked for editing, or I can allow the admin to add new items to the list.

I want to be able to link them all together and view the information in various ways. Here are just some of the "views" that I want:
- to be able to view all the events we had (just a list of events)
- to be able to view the events and who attended them in a table format (so 'x' means they showed up):
----------------------------------------------
| | Event 1 | Event 2 |
| Person1 | x | |
| Person2 | x | x |
-----------------------------------------------
- to be able to further view only the attendence of one specific event or what events one specific person went to (so basically, breaking up the above table so I can click on event 1 and get a list of names, or click on Person1 and get a list of events)
- Furthermore, when clicking on an Event, I want to see all the items that dropped and who obtained the item
- Similarily, when clicking on a Person, to also see what items he/she currently has.
- To be able to click on an item and see how many of that we have and who has it.

I am familiar w/ SQL in general, so I can create a set of queries to get the above information. I'm less concerned w/ the "reports"/"views" than with how to organize all this information (eg how many tables, relations, etc.) & creating the "front end" that'll let me do this.

What I'm visualizing is 3 "core" tables (and pardon if I use incorrect terminology, I don't really remember it all):

** Events **
primary key: id
text: Date of Event

** Persons **
primary key: id
text: Name of Person

** Items **
primary key: id
text: Name of Item

But from here, I sort of forget how to work in relational databases. Should I create "joined" tables to reflect a person attending an event? And what do I join on? Do I do something like:

Event_Person_Attend:
--------------------------------------------------------------------------
| Event ID | Person1 ID | Person2 ID| Person3 ID |
| 1 | True | False | True |
---------------------------------------------------------------------------

Or is there a more efficient way?

I'm actually pretty confident I can figure out all the DB side of this. Even if I end up not being efficient about how I do it, I'm pretty confident I can get something. My bigger problem is in creating the front-end that interacts with this.

I have no idea where to begin!!!! I don't even know what to consider! Are there open source "content management" like "programs" out there that will suite my need for this specific purpose? Or will I have to write something from ground up? What resources are available to me? What should I read? Where should I look? What should I learn?

And, thanks in advance for bearing w/ me on my thoroughly long post. I just wanted to make sure I explained what I'm looking to do.

 

[ameoba]

You probably want your "person attended event" table to have person_no and event_id and -not- have a column for every person.

If you want a list of everyone & their (non)attendance at an event, you'll want to use an outer join.


PS : describing your problem would probably make more sense if you said that you wanted to track your guild's raids...

 

[generelz]

I would think of things in complete abstract terms, first. It helps to draw things out.

The problem you are working on is called O/R Mapping (object/relational mapping). How do you map a domain of objects and their relationships into a database efficiently? There's been plenty of study on this, and the best way to go about it.

It might help if you think of the properties of the relationships between the objects, since you seem to have defined the objects in your domain. There are the a couple of key things when it comes to relationships:

- direction (who "owns" the relationship, who "knows" about the relationship)
- multiplicity (one or many)

So let's take a typical parent/child relationship first. A single parent has many children. This would be represented in UML as something like parent 1 --- * child. Typically in a parent/child relationship, the child "owns" the relationship (i.e. you say childObject.setParent(parentObject) in your code). This trickles down to your db as well - each child object will have a column that stores a reference to the primary key of the parent.

Now what about that pesky event/participant mapping? Well, this is a great example of a many-to-many relationship. Each event has many participants (users), and each user can attend multiple events. Many-to-many relationships are modeled in databases as a "join table", where each row in the table contains the primary key of both sides of the relationship. So in your case you would have a table that would have the primary key of a user, and the primary key of an event. This table's primary key would be this tuple - because each combination of (userId, eventId) is guaranteed to be unique - after all, you shouldn't be able to sign up for something twice, right?

Does that make sense? Let us know if you have any more questions.

 

[Kuo]

You probably want your "person attended event" table to have person_no and event_id and -not- have a column for every person.

If you want a list of everyone & their (non)attendance at an event, you'll want to use an outer join.

I actually thought about that as I was working on the relations and already switched to that way I'm happy you suggested it though cause I was iffy as to whether or not I should do it that way

PS : describing your problem would probably make more sense if you said that you wanted to track your guild's raids...

This isn't actually for WoW... and I haven't actually played WoW since... many years. So I don't actually know how raids work. And I figured detailing it could allow me to enlist the help of people not familiar w/ whatever game is in question.

 

[Lucio]

What you're looking at is fairly straight forward. You need three reference databases (for your events, people and items) and then a table to store the information about the event. Then you'll also need a DB to store actual users


In the info table you'll store

Line ID | Event ID | Player ID | Item ID 1 | Item 1 Lost/Gained? | Item ID 2 | Item 2 Lost/Gained? | etc... Modified On | Modified By

The transaction is simple, when someone attends an event, you simply select the event and have an "Add" button. Then fill out a simple form that then sends a line to the info database, that states whether or not an item was gained or used up.


To fufil your 2nd goal of noting which items dropped on the event, simply create a player called Event and have one of the admins fill out the same way. Then for display purposes you can query the same information in the same way as players more or less.

 

[generelz]

What you're looking at is fairly straight forward. You need three reference databases (for your events, people and items) and then a table to store the information about the event. Then you'll also need a DB to store actual users


In the info table you'll store

Line ID | Event ID | Player ID | Item ID 1 | Item 1 Lost/Gained? | Item ID 2 | Item 2 Lost/Gained? | etc... Modified On | Modified By

The transaction is simple, when someone attends an event, you simply select the event and have an "Add" button. Then fill out a simple form that then sends a line to the info database, that states whether or not an item was gained or used up.


To fufil your 2nd goal of noting which items dropped on the event, simply create a player called Event and have one of the admins fill out the same way. Then for display purposes you can query the same information in the same way as players more or less.

Your table design isn't very normalized, or even correct. How do you handle the fact that an arbitrary number of items could be lost/gained per event? You can't create an arbitrary number of columns in this table.

I would model the item gain/loss as another tuple in a join table (playerId, eventId, itemId, gained). The "gained" column would be a boolean field which would be true if the player gained/won the item at that event, and false if the player lost it at that event. Doesn't that make more sense?