Database table layout help

[Cimm]

I am making a website for an online gamming community. Basicly what I want it to do is to find out who has good/bad/neutral standings with each corporation/alliance to each other.

So a simply search for a corp name should give me a result who are what to that particular corp that was searched on.

My first table will contain corp/Alliance information.
Now, do I need to create 3 additional table's for each corp/alliance to be able to set a standing that can either be good/bad/neutral?

Anyone have any good idea's how I should go on from here?

Thanks in advance.

 

[vacunita]

I would have a corporation table, a Player table, and then an alliance table.

The corporation table contains the information for each corporation. something like:

ID Corp_Name   Corp_Data ec...

1  first Corp   some data

2  second Corp more data

...

The Player table which has the player info

Id PName     Paddress etc...
1   Player1    somewhere
2   player2     somewhere_else
...

Alliance Table

ID ID_player ID_corp  standing
1     1       1         Good
2     1       2          neutral
3     2       1          bad

etc...

This is where you set the alliances, and their standing. in the example you have that Player 1 has a good alliance with corpration 1, but has a Neutral alliance with corporation 2. see. The player cna have alliances with different corporations and have different standings within each corporation.

Then If you were to search by corporation:

SELECT *FROM corporation as C, players as P, alliance as A WHERE C.ID_corp=1 and A.id_corp=c.id and P.id=A.Player_id.

Would give you the players that have an alliance with Corporation 1 and there standings along with all their info. I'm sure the query could be done with joins. But this is just a simple example.








----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[Cimm]

Thanks,
I'll probably skip the player table, so the last code you wrote would be

ID ID_corp ID_corp standing
1 1 2 Good
2 2 1 neutral
3 2 3 Bad

etc...

Look's easy when you put it that way, I'll play around with the data and see how it works out.
Appreciate the help.

 

[vacunita]

If you skip a player table how are you going to know which players are which players or arew you only using their nicks and are not keeping track of any thing else about the players.?

Loose one of the ID_corps.

You'll still need to associate a player with the corporation somehow, so you would probably wnat somethin like:

ID Player ID_corp  standing
1     1       2         Good
2     2       1         neutral
3     2       3         Bad

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[vacunita]

Or is it the allainces amongs the corporations that you want. In that case yes
this would be correct:

ID ID_corp ID_corp  standing
1     1       2         Good
2     2       1         neutral
3     2       3         Bad

That means corp 1 has a good standing with corp 2
and corp 2 however has a neutral standing with corp 1
and Corp 2 also has abad standing with corp 3


----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[Cimm]

Nothing about the players, just corporations and alliances.

so ID_CORP can in my mind either be a Alliance or a Corp.

However, I have another questions. How will ID 1 Alliance table be the same as be the same as ID 1 in the corp table?

 

[vacunita]

If I'm understanding this right:
A Corporation is an object which can have an alliance with another corporation.

An alliance is not an object initself as it depends on 2 other objects for its existance right?

Second, The ID's will be set up when you start creating alliances.

So you'll start by creating a Corporation, this corp will get an Id. Then you need to make an alliance for the corporation. When you do this you take the ID of the corporations you want to ally. and create the Alliance.

This of course will all happen in your front end. Say a Web form.

The form then has 3 pulldown menus. One has all the corporations the second one also has corporations, and the third one has the type of standng of their alliance.

When you create your insert statement off of your webform, you'll have the ID's of the corporations put into the alliances table. Where the standing can either be Good, Bad or Neutral.

INSERT INTO alliances values ("ID_Corp1", "ID_corp2", "standing");

It is at that point that the ID's match.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[Cimm]

It is much simplier than that.

An alliance will be considered as a corporation. Since the alliance leader can set standings to anyone ie a corporation or an alliance. It doesnt have to be known which corporation belongs to which alliance. We are only interested in what standings they have to certain corporations or alliances.

Yes it will be web based as you described.
Right now I have one table called

cm_members - which will contain Corporations/Alliance names along with other informations. The only thing that is interesting from this table is the ID and the name

cm_members.id & cm_members.name

The second table cm_standings has what we spoke about yesterday.

ID ID_corp ID_corp standing
1 1 2 Good
2 2 4 neutral
3 2 3 Bad

etc...

This is working fine for now.

Here's some web code in php I did. Perhaps the code will explain further what I am trying to do.

$query =
"SELECT
cm_members.id, cm_members.name, cm_members.email,
cm_standings.id, cm_standings.corp1, cm_standings.corp2,
cm_standings.standing
FROM cm_members, cm_standings
WHERE ((cm_members.id = cm_standings.corp1))";

$result = mysql_query($query);

$number = mysql_numrows($result);

print "There are currently $number Corporation/Alliances with Standings in this database:<p>";

for ($i=0; $i<$number; $i++) {
$name = mysql_result($result, $i, "name");
$corp2 = mysql_result($result, $i, "corp2");
$standing = mysql_result($result,$i,"standing");
print "$name, $corp2, $standing<br>";

The result will be "corp name", "4" , "Good"
Allmost correct. The 4 is supposed to have a corp name of what ID 4 is in cm_member table. Still playing around with that.

To go further, there will be an admin page where you go in and insert value's for each standing's.

For now I just want a showstandings.php web page.

Thanks again for your comments and idea's.
//Mike

 

[guelphdad]

SELECT *FROM corporation as C, players as P, alliance as A WHERE C.ID_corp=1 and A.id_corp=c.id and P.id=A.Player_id.

Would give you the players that have an alliance with Corporation 1 and there standings along with all their info. I'm sure the query could be done with joins. But this is just a simple example.

you do realize what you have written IS a JOIN only in list syntax instead of with an explicit ON clause right?