Thoughts on the DB Design for a Members Budgeting App

[jhc1982]

Hi Guys,

 

I thought I would get some advice on the best DB structure for my first php project and would love to hear your thoughts.

 

The web app is very basic. Essentially I am providing a members only website where people can keep track of money owed to them. A good example would be a freelancer who wants to keep track off all money owed to them by various clients.

 

I want to have predefined categories for people to choose from. ie, employer, friend, tax rebate, rent on investment properties etc but also have the ability for people to add their own categories.

 

The app will essentially display to users how much is owed to them in each category along with line items. An example of this could be:

 

Employers (2 outstanding debts):

$2400 for web project                | ABC Inc

$1000 for recruitment services    | 123 Inc

 

Friends (1 outstanding debt)

$20 for the bus!                          | Jonny Mayo

 

My thought was that I just need a members table with standard, ID, email, Password, City, Country, Registered Date. A category table with CatID, CategoryName. And then I get confused as to whether I should have a table for each users debtor list or just one (potentially very long) combined debtors list from all users. My thought is that it is best to have a table per user.

 

Am I missing anything major here? Any thoughts on improved structure?

 

Thanks

[Adam]

And then I get confused as to whether I should have a table for each users debtor list or just one (potentially very long) combined debtors list from all users. My thought is that it is best to have a table per user.

 

Define 'very long'? MySQL can cope perfectly fine with millions of rows, provided you index it correctly. There's no need to make querying and maintaining the tables more complex than it needs to be. Also you loose out on being able to produce and stats or reports over multiple users.

[jhc1982]

And then I get confused as to whether I should have a table for each users debtor list or just one (potentially very long) combined debtors list from all users. My thought is that it is best to have a table per user.

 

Define 'very long'? MySQL can cope perfectly fine with millions of rows, provided you index it correctly. There's no need to make querying and maintaining the tables more complex than it needs to be. Also you loose out on being able to produce and stats or reports over multiple users.

 

Not millions and millions or rows for sure!

 

Would you be happy with the structure I suggested in that case?

 

Thanks for the help!

[shlumph]

Based on your description, I'd probably have something like the following:

 

Users

UserID
email
Password
City
Country
RegisteredDate

In the above table you can keep track of all the users

 

Categories

CatID
CategoryName

In the above table you can keep track of categories; ie: employers, friends, etc.

 

Items

ItemID
CatID
UserID 
Description //ie: for web project
Price //ie: $2400
Client //ie: ABC Inc or Jonny Mayo

In the above table you can relate the user and categories, as well as adding a description and price.

 

Depending on how far you want to go with it, you could create another table (or two: one for individuals and one for companies) to store the Client field.

 

It's also useful to have fields like; date_added, date_modified, status, etc. Hope this helps.

[jhc1982]

Based on your description, I'd probably have something like the following:

 

Users

UserID
email
Password
City
Country
RegisteredDate

In the above table you can keep track of all the users

 

Categories

CatID
CategoryName

In the above table you can keep track of categories; ie: employers, friends, etc.

 

Items

ItemID
CatID
UserID 
Description //ie: for web project
Price //ie: $2400
Client //ie: ABC Inc or Jonny Mayo

In the above table you can relate the user and categories, as well as adding a description and price.

 

Depending on how far you want to go with it, you could create another table (or two: one for individuals and one for companies) to store the Client field.

 

It's also useful to have fields like; date_added, date_modified, status, etc. Hope this helps.

 

Thanks man.

 

That makes perfect sense and I will go ahead with that structure.

 

This is a great forum and helping out loads in my early php/mysql day so i'll be sure to contribute once I am more up to speed!

 

Thanks again