Preserved Topic: Database layout

I've been helping someone code a site with PHP/MySQL. Users who come to the site will choose a mutant-pup (don't ask) of their own and eventually be able to equip it with items to battle with. This brings me to my question. The kid I'm helping wants the person to be able to have any combination of items (even more than one of each) up to 10 items total. So a user could have say 2 swords, or three lances, or whatever (you get the idea).

So, if my database has 10 fields to hold items, what is the easiest way to go about checking for the next empty slot to put an item in. If field 1 and 2 hold items, I want the code to realize that they are full and put it in the next available empty slot in the database table, or if all 10 slots are full, send a message saying so. I'm sure I could do this using just PHP, but are there any MySQL functions I haven't learned yet that can help in this situation.

The young man currently has everything in one database table (username, password, pup_info, items) and I'm assuming to do this in the most efficent way, I should tell him to have a seperate table for just the items.

Thanks for your help!

-Butcher-

butcher: I'm more in favour of more tables. I would do it this way:

main_table
user_id
user_name
ETC

weapon_table
weapon_id
wepaon_name
ETC

weapon_link_table
user_id
weapon_id
weapon_number

you could then have user 1 with 3 of weapon 2 and 1 of weapon 3, etc.

That would be the way I'd do it anyway (I'm sure everyone has different ideas on this as it is a greyish area I suspect). You could check, for example, that the total weapon numbers for a specific user_id are less than ten before letting the user increment their numbers or add another. If that isn't clear I can go into more detail.

Emps


You're my wife now Dave

emps, that layout is very good! you solved the linking problem with a third table.
to be correct, the link table should look like

weapon_link_table
user_id
weapon_id
weapon_number
FOREIGN KEY (user_id) REFERENCES main_table(user_id) ON DELETE CASCADE
FOREIGN KEY (weapon_id) REFERENCES weapon_table(weapon_id) ON DELETE CASCADE

GRUMBLE: Thanks. I've not used foreign keys before. I'm quickly reading through the documentation:
www.mysql.com/doc/A/N/ANSI_diff_Foreign_Keys.html
www.mysql.com/doc/I/n/InnoDB_overview.html

and am I right to assume that you need to recompile MySQL to get Foregin Keys to work? If so this wouldn't be suitable for quite a few people's hosting services. It all looks very interesting though and I will have to stop skimming the material and print it all off for a proper read. Thanks,

Emps


You're my wife now Dave

recompile? what do you mean by that?

GRUMBLE: I was just wondering as:

quote:

---

In MySQL 3.23.44 and up, InnoDB tables supports checking of foreign key constraints. ... For other table types, MySQL does parse the FOREIGN KEY syntax in CREATE TABLE commands, but without further action being taken.

---

and:

quote:

---

To compile MySQL with InnoDB support, download MySQL-3.23.34a or newer version from http://www.mysql.com/ and configure MySQL with the --with-innodb option.

---

As I'm not familiar with the use of Foreign Keys I was wondering about how one would go about setting it up. 'Recompile' might not have been the proper term I was looking for but you appear to have to change the configuration of MySQL which isn't an option for quite a lot of people. However, I am just skimming the literature and could have the wrong end of the stick (or is this something everyone knows about and I've missed? Typical!!). It does look like an interesting technique so anything extra information you can supply would be helpful.

Emps


You're my wife now Dave

duh, i dont know much about InnoDB (infact, i only know what you just posted here ) but the foreign keys are to make sure that a user cant have a special weapon twice or more times.

a key in general is the item in the table which identifies the dataset. so, you also will have to add
PRIMARY KEY (user_id)
to the main_table. (and also for the weapon_table of course.
and a foreign key is also a identification key but in an other table.
and that isnt MySQL specific, that's just SQL in general.

(hope that wasnt too confusing )

sorry, emps, im not so familiar with the technical details of mysql.

GRUMBLE: Thanks for the quick overview and pos. someone else can jump in and tell us if this is commonly available in MySQL. It looks like an interesting feature. I'll continue reading and see what else I find (I'm off home now but will check in later).

Emps

*ahem* foreign keys are not an interesting feature, they are very important elements of a db-structure.
or were you talking about that innodb thingie?

Hi.
Not beeing a DB-Guru (but sharing desk with one...) I know a bit on this.

1. MySQL has not been supporting foreign keys at all. Just recently I have heard that it's coming (wether you need to compile in something for it to work I don't know).

2. Foreign keys is indeed an important factor in a good relations databse.
As I've been taught, you mainly use them to preserve database integrity in the relations.

Let's say you split a one big table db to several tables to store separate things.
As you normalize this DB on for instance user data, you will certainly have one table for country and one for users (more than one user in one country, but only one country for each user).

Then, since more than one user in theory can live on the same address, maby you want the addresses in a table of their own and so on.

Like this:
users
usr_id
userfirstname
user lastname
address_id (the id that the users address is stored under in the table "addresses" = foreign key )
country_id (the id that the users countryis stored under in the table "country" = foreign key )

In doing this, you set different kind of relations between for example the user, the address, and the country. This is so you can update a familys address in one place (the address table) instead of on all the 5 rows for the family members. Or worse yet, a country is renamed, please update these 250.000.000 rows... These id's that you save in the user_table now needs to be maintained/updated, plus checked so they are valid id's and so on.
This you can either do soley via SQL-queries or stored procedures (a must if the db don't support FK), but this is tedious and easy to slip up. If you use the support for FK properly (like in GRUMBLE's example above) you save yourself a lot of coding and you will have yourself a db-design with a lot more integrity.

(phew, didn't think I could describe that...)
There is probably more ppl that can shed some more light on these parts of db-design, it's a very big area and I'm just sniffing in the corners for now.
/Dan


-{ a vibration is a movement that doesn't know which way to go }-

Thanks Emps!

That sends me in the right direction.

As for the foreign keys in MySQL, I was just recently reading an ongoing debate in another forum about the pros and cons of certain DBs. One of the knocks on MySQL was it's lack of support for foreign keys. It was a thread started in the very beginning of this year, so if a new release of MySQL has come out since then this may have changed. If it has, I would love to have someone point it out.

<edit>

I swear DmS post wasn't there when I started typing mine, or I wouldn't have made the same point... I swear it wasn't there

</edit>



[This message has been edited by butcher (edited 12-13-2001).]

All - that seems to sum up my current understanding of the situation. Foreign keys are very important (esp. the way I do DBs with vast numbers of tables!!) but their support in current implementations of MySQL isn't standard which for the current problem is the important bit (what I said intially should work fine). However, I would be interested in anything other people have on this esp. the future support of FKs in MySQL.

Emps

From the horses mouth ( http://www.mysql.com/doc/M/i/Missing_Foreign_Keys.html ):
****************
Note that foreign keys in SQL are not used to join tables, but are used mostly for checking referential integrity (foreign key constraints). If you want to get results from multiple tables from a SELECT statement, you do this by joining tables:

SELECT * from table1,table2 where table1.id = table2.id;

See section 6.4.1.1 JOIN Syntax. See section 3.5.6 Using Foreign Keys.

The FOREIGN KEY syntax in MySQL exists only for compatibility with other SQL vendors' CREATE TABLE commands; it doesn't do anything. The FOREIGN KEY syntax without ON DELETE ... is mostly used for documentation purposes. Some ODBC applications may use this to produce automatic WHERE clauses, but this is usually easy to override. FOREIGN KEY is sometimes used as a constraint check, but this check is unnecessary in practice if rows are inserted into the tables in the right order. MySQL only supports these clauses because some applications require them to exist (regardless of whether or not they work).

In MySQL, you can work around the problem of ON DELETE ... not being implemented by adding the appropriate DELETE statement to an application when you delete records from a table that has a foreign key. In practice this is as quick (in some cases quicker) and much more portable than using foreign keys.

In MySQL 4.0 you can use multi-table delete to delete rows from many tables with one command. See section 6.4.6 DELETE Syntax.

In the near future we will extend the FOREIGN KEY implementation so that at least the information will be saved in the table specification file and may be retrieved by mysqldump and ODBC. At a later stage we will implement the foreign key constraints for application that can't easily be coded to avoid them.

In MySQL 3.23.44 and up, InnoDB tables supports checking of foreign key constraints. See section 7.5 InnoDB Tables.

************
End horses mouth speak...
/Dan


-{ a vibration is a movement that doesn't know which way to go }-

DmS: Thanks for that (somehow I missed that one). I look forward to being able to use them at some point.

Emps