This club membership system is made in java. What i have done already is a database with 1 table called accounts where the registered members and admins are stored, the fields of this table are username, firstname, lastname, password, date_join, role etc...
what our program do so far is if the person register, then he is automatically a member until an admin promote him as an admin also, a member has limited privileges in the system unlike the admin with full privilege.
Our professor says that we need to revise our program and add a payment requirement for anyone who wants to join in our club, and the membership should expire in a certain amount of time.
Due to our lack of experience in clubs, we don't know how we should implement that additional feature. Is it like we need to create another table for payments and link that to accounts table? And in the program, is it the registering user should pay first a certain club fee before he could register successfully? or is it he could register now then pay later? and when his account expires, is it he will be removed from the database if he didn't renew his membership?
Any one who has an idea about this kind of system, and could suggest a good implementation? Thanks in advance.
Start off simple, name all of the nouns you wish to manage. Those are your table names.
Then think of all of the adjectives pertaining to each noun that you might want to track. If a group of adjectives fit into a category, then create a column within the associated table. If an adjective always applies to every possible noun, then just disregard it. Databases are meant to track changing data over time, constants are best not stored.
Then think of the adjectives that cannot be missing. Perhaps a person's birth date can be missing if you allow opting-out of a birthday reminder. Perhaps your club is a Birthday oriented club, and having a missing name would be tantamount to pandemonium.
Then look at the required adjectives. Find a few that by themselves, or in combination, identify an entry in the table. For some, a Person might be identified by their name, meaning that every non-identical name results in accessing a different Person, and every identical name results in accessing the same Person. If you have ever met two John Smiths, or have ever changed your name due to marriage, you can immediately see that a name column alone is not an ideal way of determining identity. The collection of identifying fields is called the primary key.
Then think of how the nouns are related. If a Membership is not the same as a Person, then you will need to join the two together. There are three basic techniques for joining tables, and there are more advanced techniques that are probably best applied later.
One X (Person) can only be associated with one Y (Membership), then the X and Y tables might be a candidate for merging into one table. This is called a 1:1 relationship.
One X (Person) is associated with many Y (Memberships), then the Y (Membership) table should contain a foreign key reference to the primary key of X (Person) it is associated with. This is called a 1:N relationship.
Many X (Persons) are associated with one Y (Membership), then the X (Person) table should contain a foreign key reference to the primary key of Y (Membership) it is associated with. This is called a N:1 relationship
Many X (Persons) are associated with many Y (Memberships). In short, a person might have a specific membership, be part of a group membership, might have bought into multi membership package, and / or might only have a single membership. In this case, you need to manage the relationships with a constructed "relationship" table, which contains two foreign key references, one to each primary key of X and Y. Finally, these two foreign key references probably should (in combination) be the primary key of the relationship XY (PersonMembership) table.
There are other ways of joining tables, but basically they are extensions of the last technique, where you bump up the number of joined tables.
This will give you a good start, and you'll soon see that the choices you make will not be the same choices that anyone else will make, which is why there are very few standard database table sets. Once you get a basic framework up and running, you'll find that you might always pull out two sets of related data at the same time, and then (if you go that far) you might consider breaking a few of the rules for performance. Just don't try to break the rules from the start; you must follow the rules to know when to break them. That way, you'll know what you are losing in gaining your objective, and that you do avoid the introduced pitfalls in your actual usage.