TechHui

Hawaiʻi's Technology Community

Aloha, I'm working on a personal project involving php and mysql. I'm way rusty, but I have a few ideas and am looking to my peers for some validation on how to design the database - I figure since all the information is stored their, it's more important then the front end, so I want to get it right the first time.

The project is basically taking a spreadsheet I made for Castle Age (it's a Facebook game that I'm enjoying) and turning it into a multi-user web page (each user would have their own account that would store the various financial data within Castle Age, tracking how much land they own (income) and how much weapons, soldiers, and generals they own (expenditure)).

Due to the dynamic nature of Castle Age and its updates (new stuff being added all the time) I couldn't take the easy way out and just hard code the available data into the database, so I'm looking to make sure I'm headed in the right direction at least.

I have the 3 following tables to start with:

"Accounts"
userID, user, pass, email, balance

"Land"
landID, landname, landinitcost, landincrementcost, landincome

"UserLand"
userID, landID, numownedland

Accounts and Land both have a unique primary key that auto-increments, userland has NO primary key thus far, as I'm hoping to tie userland.userID = accounts.userID & land.landID = userland.landID

Again, this is just a project for fun and personal use amongst me and some friends - but am I on the right track using 3 tables so far?

Views: 64

Reply to This

Replies to This Discussion

Sure, looks good so far.

For the UserLand table, you don't necessarily need to have a surrogate key like the userID and landID columns in the Accounts and Land tables. MySQL (and other database engines) let you define a composite primary key, which is a primary key made with multiple columns. For a throwaway app, it's probably not worth the trouble, but it's something you may want to look into for other designs.

Do you want to be able to track the changes in land and property ownership over time? You may want to add a createtime, modifytime, deletetime, etc. column.

I don't play Castle Age, but depending on how land, soldiers, and weapons behave, you could either create separate tables for each, or combine them in a single table called Items or something like that. I'm not sure which way is better, but my wild guess would be separate tables.
I actually decided on separate tables for each item (land, soldiers, generals, weapons), I just didn't include that info as I was trying to keep the post simplified.

I didn't think about adding a log of when you bought and sold land / soldiers / weapons / generals, but's on the to-do list now.

Aside from all that though, am I on the right track? :P
Sure, looks good.

Rick Payton said:
I actually decided on separate tables for each item (land, soldiers, generals, weapons), I just didn't include that info as I was trying to keep the post simplified.

I didn't think about adding a log of when you bought and sold land / soldiers / weapons / generals, but's on the to-do list now.

Aside from all that though, am I on the right track? :P

Reply to Discussion

RSS

Sponsors

web design, web development, localization

© 2024   Created by Daniel Leuck.   Powered by

Badges  |  Report an Issue  |  Terms of Service