1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

What is the best method for optimizing a member database?

Discussion in 'Databases' started by snpsql, Dec 22, 2020.

0
  1. #1
    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
     
    snpsql, Dec 22, 2020 IP
  2. seedling

    seedling Active Member

    Messages:
    34
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    93
    #2
    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
     
    seedling, Dec 22, 2020 IP
    Efetobor Agbontaen likes this.
  3. snpsql

    snpsql Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3
    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. :)
     
    snpsql, Dec 22, 2020 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #4
    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
     
    sarahk, Dec 22, 2020 IP
  5. snpsql

    snpsql Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #5
    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. :)
     
    snpsql, Dec 22, 2020 IP