Hello Forum, In the past, over 8 yrs ago or so, I created a personal Family based website where i used MySql for family member general static type information. I have used excel for many programs i created and one of them is for my brother's church where I created a yearly member database that kept track of each members donations etc.. The way I had the excel database structured was.. 1. 1 File for Each Member had 12 sheets for each month of the year 2. Each Month had 10 lines to cover any donations etc for that month So, what I am trying to ask, hopefully without being to confusing lol, is.. Can I use One (1), database to hold EACH member that would handle possible multiple payments each month? (If Possible). Or, will I have to have 2 SQL databases for each member, One(1) for each month with up to 10 possible inputs for each month and the other for each months totals? Thanks for any helpful info on this in Advance.. snp
You should be able to do as much data as you want by creating more than one table. Each table will need a USER_ID to relate the data from one table to another. One table can hold the user_id and the donation (including a date). Another thing, you should also look into SUM(). Here is a link on using SUM(). https://www.w3schools.com/sql/func_mysql_sum.asp Look in to creating an ER diagram. https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model If you need more help, feel free to message me. If you contact me send this link so I know which forum post you are using: https://forums.digitalpoint.com/threads/what-is-the-best-method-for-optimizing-a-member-database.2869278/#post-19692978
Ahh, didn't look at it like this.. Makes much sense. I had some medical issues over the last 4 years which got me out of my programming mode for a while alone with my age make it seem as if I'm lost and starting all over again.. lol. Never was a guru on SQL stuff but back then I had my family website going quite well with member data, food recipes, and other SQL stuff, but I am quite out of the loop at this moment. Anyhow, Thanks you for the helpful info.
The first thing you need to get straight is the distinction between a database and a table. Effectively you're just recording "sales" without reference to a stock item. To start with you'll have Table: "people" person_id name phone address created (timestamp) modified (timestamp) Table "donations" donation_id person_id amount dategiven method (auto payment? envelope at church?) created modified This query will give you their total donations for 2020 select people.name, sum(donations.amount) from people left join donations on people.person_id = donations.person_id where year(donations.dategiven) = 2020 group by people.person_id order by people.name
Thanks sarahk, that is proper and well explained. I used to knock my head back and forth on the best way to categorize items on my pc.. example: Is it better to categorize by date then types (or occasions), verses types then dates.. for me, it appear they just about work the same depending on how you intend to access them. Another scenario would be; would some people prefer to keep images and videos in separate folders even if they are from the same events or put them together because they are from the same events.. With that said, you gave a great example of using the database/table structure for my purpose.. Thanks Much.