Need help structuring a database...

[G3mInI]

I run a clan site for the game Medal of Honor. What I want to do is have a members page that is created dynamically from php. The ability to add/remove members via a form, and to also edit members as they get promoted.

The information we have on our members page now:

Rank Image (a gif image)
Member Name
Member Location
Enlistment Date

I basically would just like some advice on what a good mysql (MyISAM type) table structure would be for what I am trying to do.

Then I will have to delve into the proper sql query to pull that data out, and format it into a nice html table.

Here is our members page as it stands now... I would really like to get it to look like it does now but be driven dynamically by php instead of a static html page.

[a href=\"http://www.area51moh.com/A51-Members.htm\" target=\"_blank\"]http://www.area51moh.com/A51-Members.htm[/a]


This way I could have admins easily use a form to make any changes via a form and not have the need for them to have html editing abilities.

I just don't know wether to make the database one big table with all the info, or to split it up into smaller tables like name and location in one, and rank name and image in another? And the whole foreign keys thing to me is, well, it's definitely foreign. :(

So thanks in advance if anyone is willing to send me in the right direction on this little project of mine,

Regards,
G3mInI

[Cep]

Basically the first thing is to write down all your values,

name,
age,
place of birth,
etc etc

If you have loads of fields then group them into manageable chunks and then base tables of these. The main reason for creating more then one table is in case there is information which is repeated in a column quite a lot.

For example your ranks, there is no need to repeat the word "major general" a hundred times in one column, the same rank image URL in another column and then a further rank information column all displaying the same information, if you can just have a simple reference ID number under one column and a small table with your ranks where the reference ID is the primary key of your ranks table. e.g. 1 = general = general.gif.

If however you only have about, I dunno 10 - 15 fields you may as well create the one table :)

[G3mInI]

Yes, I have been giving this thought since I posted.

I have thought of using the following two tables.

Table Members:

member_id smallint (auto increment) primary?
name varchar(255)
location varchar(255)
enlisted date? or varchar(255)?
rank_id tinyint


Table ranks:

rank_id tinyint (primary?)
rank_name varchar(255)
image_name varchar(255)
image_alt varchar(255)
image longblob



See here I am not sure for the enlisted date wether or not to use date type or varchar for that field.
Also I am not sure about which keys I should make primary.
All my rank images are the exact same size and I have now made them all jpg files. Average size is 1.5kb, so I am not sure if I really need longblob or not, I dont know the limits of blob and mediumblob.
I put in the image_alt so I can use that in my table for the ALT part of the image tag.

Once this is set up, I begin to get real confused as to how to set up a query which will put it together for me.
I can do simple queries to get the information I want, but I do not know how I would query this in a way that the information would produce a table in a while loop and have it sorted by rank_id since I entered my images in order of Army ranking, rank_id #1 being General of the Army (5 star) and rank_id #28 being a private.

I guess where I get confused is how to get information from two tables.

I need this to produce a table that has 4 columns, the rank image, member name, member location, and enlistment date. And of course however many rows is needed using a while loop.


G3mInI

[Cep]

Firstly that is a good start.

And yes you have labelled the correct fields as primary keys and they shouold be set to auto_increment.

I would perhaps reduce the size of your varchar fields to probably 25 - 50 characters, there is no need to have 255 spaces, it just bloats your database.

For your date field it really depends on how you retrieve (and what you do) with that information. If you are going to perform some sort of working out on it, choose a date. If its purely for display you can get away with varchar.

I personally do not use blobs (mainly cos I dont really understand them myself ;D ) but instead I tend to build an html img string in PHP using the information retrieved from my tables.

Plus again I am guessing that with a blob you increase the size of your database quite a bit.

Getting information from two tables is fairly simple.

All you do is reference the ID in one table from the ID in another.

For example a rough SQL for your situation would be,

SELECT * FROM members_table, ranks_table WHERE members_table.rankid = ranks_table.rankid;

This would select all fields in both tables where the rank ID is the same. You can also add an AND clause to the end to further refine your query. For example if you were pulling the information about a specific member via a php variable you would do,

SELECT * FROM members_table, ranks_table WHERE members_table.rankid = ranks_table.rankid AND members_table.memberid = {$myIDvariable};

[Barand]

Use DATE type fields for dates. You then have dozens of date functions at your disposal, you can format it any way you want on retrieval, you can select ranges of dates, sort by date. If you store it as a formatted varchar, all you can do is look at it.

[G3mInI]

Thank you very much for the information above. I now have a good start on this project and it is coming along nicely. Sorry to have taken so long to reply, but I have become quite involved in setting up a dedicated server for my gaming clan.

Again thanks for the info,
G3mInI