Hi guys

At the moment i,m designing a database for a web project i,m doing, but I wanted to ask your advice on something.

Its basically about relationship modelling.

In mysql, say if you have two tables, each with a primary key(id), and you want to combine both primary keys into a third table (which would be a one to many relationship) , how to do go about preventing the same two vaules from occuring twice in the third table?

Is it simply a matter of creating a primary key out of the two fields in the third table or is there more to it than that?

Thx guys

RecoilUK

Not knowing exactly what you want to do I can only summize but each table should have it's own primary key independent of any other table. This is important/safe/reliable when updating tables.

Table 1 has pkey id1
Table 2 has pkey id2 and foriegn key id1 (from table1)
possible index on id1+id2
Table 3 has pkey id3 and foriegn key id2 (from table2)
possible index on id2+id3


Your join is t1.id1 = t2.id1 and t2.id2 = t3.id2

Depending on your data you may need outside joins.

No flames here, but mysql is not a real relational database. It's more like a data store with sql queries.

Foreign key relationships setup in mysql are ignored.

Hi guys

Crabboy - whats your point exactly? does this impose any limitations or special considerations to be aware of when designing tables.

te_conway - Suppose I were to put in a pkey for a table, lets say its table1, that pkey would ofcourse be unique to that table, but what if a had two further fields in that table which also have to be unique, is this possible? the two fields in question are fkeys btw.

Thx guys

RecoilUK

Hi guys

Ok, let me describe what i,m trying to do, so you understand it more.

Table one is called teams_table (pkey=team_id) and table 2 is called leagues_table (pkey=league_id).

Each team must be able to enter in as many league,s as they like, but, not in the same league twice, how would you go about doing this?.

Thx guys

RecoilUK

Relationships have as much to do with design as the database itself. True MySQL isn't a true relational database but neither is oracle if you don't set proper relationships. Because Mysql isn't relational you can't do cascade updates, deletes, etc.

There are several ways to do what you want. If you were assigning a team to a league it would be a simple matter of having a league_id in the teams table. Since teams can join multiple leagues I would create a third table and use it for subsequent selects. If you don't, either your team or league table will have lots of duplicate info. Consider this:

[Teams]
tid name coach
--- ---------- ------------
1 Patriots Belachuk
2 Rams Martz

[Leagues]
lid name Division
--- ---------- ------------
1 NFL NFC West
2 NFL AFC East

[TeamLeague] primary key is tid(team) + lid(league)
tid lid
--- ---
1 2
1 1
2 1
2 2

Get all the data like this:
Select t.*, l.* from teams t, leagues l, teamleagues tl
Where (t.tid = tl.tid and l.lid = tl.lid)

The same thing w/o a 3rd table. Here the pkey MUST be a two field combo and if any of the content changes, it has to be updated in more than one row. You carry lots of unnecessary data and it becomes very high maintenance.

[Leagues]
lid tid name Division
--- ---------- ------------
1 1 NFL NFC West
1 2 NFL AFC East
2 1 NFL NFC West
2 2 NFL AFC East

-Tom

Hi again

Thx tom, thats wat I thought actually, it was just the fact that the third table didnt have its own independant id field that was throwing me, and I wasnt sure if you could issue a joint primary key in MySQL.

Thx again, hope I can repay the favour sometime

L8rs

RecoilUK

Using innodb tables in mysql makes foreign keys available.

Read the manual &&|| on their site.

Hi guys

Yeah thx for the info, although I think they are still in beta, so I wont be using them just yet, i,ll just join table for now.

Actually, while i,m here, I may aswell ask another question. My database design so far is based on collecting info from users and putting it into a database, which is in itself not very hard. But, the next thing I shall be working on, is designing the table structure for the actual article,s and menu driven url information for site navigation.

Does anybody have any tips on this, or know of some really good tutorial about it?

Thx again guys

RecoilUK

RecoilUK,

I'm not sure what you're looking for, an html editor?

-tom

Hi Tom

No not really, I know html allready, what i,m looking for is advice on creating a content management system for my project.

For instance, the site is divided into three parts, the title and add banner, the left hand menu/site navigation and the content.

What I cant quite understand is how the menu links on the left hand side, control the content. With html its easy because for every link you have a seperate page, but with PHP everything is pulled out from a database, so far i,ve come to the conclusion that templates are used but i,m also not sure how this is accomplished.

If anyone can offer advice on the above, links to tutorials or even really good books that actually cover the above question, I would be most gratefull.

L8rs

RecoilUK

Maybe I don't understand. Php is just a way to present and store data thru web pages like cgi perl.
The content logic isn't going to change, your menu will call individual php pages. the Php page might be a form with a method of post to save the data. The post is going to reference another php page.

I would look at www.php.net and links within that page.