[RESOLVED] Relational database design advice

[knucklehead]

Hello everybody!

I have a task to make a PHP/MySQL website for some music club, and of course, first thing to do is to create and design a good database, which will be useful in the future...

Since this is my first time to design such a 'complex' db :rolleyes: I would like to check if I understood the concept of relational tables correctly so please help me with this easy one =)

Basically, I started with two 'main' tables: Events and Artists.
I created relations in this way:

One artist can play on one or more events and one event consists of one or more artists.
Furthermore, one artist (band) can come from one or more countries and one country can be home for one or more artists.
One artist can have one or more links to their websites, and one website link can be a home page for one or more artists.

One event can be located on one or more locations in town, and one location can be a place for one or more events.
One event can have one or more flayers/posters and one flayer/poster can be a promo for one or more events.

So, I had to get rid of all those many-to-many relations and I created 5 junction tables: artists_events, artists_countries, artists_links, events_locations and events_flayers.
All junction tables have only 2 columns and both of them are foreign and primary key. Each of column is related to primary key of a 'parent' table.

Please tell me if I'm doing this correctly so far??

Right now I'm stuck on a 'Gallery' table because it is related to 3 more tables: Pictures, Artists and Events.
Relations go like this:

One gallery can have one or more pictures but one picture can exist in only one gallery.
One gallery can represent one or more artists and one artist can exist in one or more galleries.
One gallery can hold only one event, but one event can have one or more galleries.

I wonder if this is a good approach to my 'problem'? I am not sure exactly how to make relations between Gallery-Artists-Events here... Can I create another junction table which will have 3 columns which will be related to primary keys of those 3 tables or is there another method to accomplish what I need?

Any suggestions/advice/help is very welcome and appreciated!
Thanks a lot!

[johanafm]

Looks good at a quick glance.

I'm just guessing that your gallery is supposed to be assigned pictures manually.
For this, I'd put the gallery_id in the picture table (1:m relation from gallery to picture), one gallery_to_artist table and finally put the event_id in the gallery table.

Else I'd deal with the last part by keeping two tables for picture relations:
artist_to_picture and event_to_picture and skip the gallery table.
In that way, you can get all pictures from an event, or of an artist. Or combining both, of an artist at a specific event. Sort of an autogenerated gallery.

[knucklehead]

Hey, thanks for turbo fast reply =)

Yes, you're guessing right about manually assigning of pictures... And I started to do something like your first example but in one moment it just started to get more and more confusing =)

Anyway, I think I should go with your first method because I have some extra informations about the gallery itself in table 'Gallery'...
Although the second method seems much better to me... =)
I will try to experiment a little and report back...

Thank you very much for help!

[knucklehead]

Hello again =)

...after considering all "client's" needs, I decided to take a first solution (to leave a 'Gallery' table) so I think I finally completed my ER diagram...
So, what next?
My idea is to create a class, let's call it Artist, and hold all artist's data inside of it. Another class would be definitely Event class. After that I could handle all the most important data very easy, right? Maybe classes for flayers/posters and locations would be handy too?

Anyway, I'm stuck in a first step, in creating an Artist class. I created few properties and a constructor which passes some variable which is actually an artist's ID. Inside of constructor is a mysqli query which gets artist's information - country, links, description, etc. and for testing purposes I put few echo lines inside while loop and got something logical, but not what I needed...

Since some artists have more than one link, my query outputs more than one row but I want to display it as only one artist with only one description and as many countries and links there are...

I'm not sure should I accomplish this with combining few queries or is this possible with some 'weird' sql query, or should I iterate somehow in php through my result? I have read somewhere that you cannot use foreach() on mysqli results?? So how to resolve my problem? Please help me =)
Thanks in advance!

my class:

class Artist{

// few properties
public $artist_name;
public $artist_country; // this is an array
public $artist_links; // this is an array
public $artist_desc;
public $artist_priority;

function __construct($AID){

	$mysqli = new mysqli('localhost','root','**','**');

	$query = "SELECT 
				artists.artistName,
				artists.artistDesc,
				artists.artistPriority,
				artistLinks.artistLinkURL,
				artists_links.*,
				countries.countryName,
				artists_countries.*
			FROM 
				artists,
				artistLinks,
				countries,
				artists_links,
				artists_countries 
			WHERE 
				artists.artistID = $AID AND 
				artists.artistID = artists_links.artistID AND 
				artists_links.linkID = artistLinks.artistLinkID AND 
				artists.artistID = artists_countries.artistID AND 
				artists_countries.countryID = country.countryID";

	$result = $mysqli->query($query);

	while ($row = $result->fetch_object()){
		echo $row->artistName."<br />";
		echo $row->artistDesc."<br />";
		echo $row->countryName."<br />"; // what if there's more than one country?
		echo $row->artistLinkURL."<br />"; // and more than one link?
	}
}

}

[johanafm]

You could either select also artist.id (or whatever your primary key is called) as well as ORDER BY artist.id, and then

$lastId = 0;
while ($row = $result->fetch_object()){
	if ($row['id'] != $lastId) {
		$lastId = $row['id'];
		echo $row->artistName."<br />";
		echo $row->artistDesc."<br />";
	}
	echo $row->countryName."<br />"; // what if there's more than one country?
	echo $row->artistLinkURL."<br />"; // and more than one link?
}

or you could skip the order by and

$artists = array();
while ($row = $result->fetch_object()){
	$artists[$row['id']]['artistName'] = $row->artistName;
	$artists[$row['id']]['artistDesc'] = $row->artistDesc;
	$artists[$row['id']]['countries'][] = $row->countryName;
	$artists[$row['id']]['links'][] = $row->artistLinkURL;
}

foreach($artists as $artist) {
	echo $artist['artistName'] . '<br/>';
	echo $artist['artistDesc'] . '<br/>';
	foreach($artist['countries'] as $country)
		echo $country . '<br/>';
	foreach($artist['links'] as $link)
		echo $link . '<br/>';
}

[knucklehead]

Hi and thanks for help!
I tried both examples but I think there is an error in syntax here? 😕
I cannot get to my rows with $row['id'] if I'm using mysqli, right?
Anyway, neither of examples give me output I need, and that is:

artist name
artist description
artist countries (comma separated values or whatever)
artist links (like countries)

If I'd replace $row['id'] with $row->id in both examples I'd get this output in first example:

artist name
artist description
artist country 1
artist link 1
artist country 2
artist link 1
artist country 3
artist link 1
artist country 1
artist link 2
artist country 2
artist link 2
artist country 3
artist link 2

and something like this in second example:

artist name
artist description
country 1
country 2
country 3
country 1
country 2
country 3
link 1
link 1
link 1
link 2
link 2
link 2

This brings lots of confusion in my head =) Please help!
Thanks in advance!

[johanafm]

That was a misstake on my part since I tend to use fetch_array or fetch_assoc, which populates an array with result set data rather than an object. So yes, $row->id is the way to go with your fetch_object call.

And the reason you get two sets of links and two sets of countries is due to the table joins. Let's say data relating to one artist consists of

artist				country		links
----------			--------		------
metallica			usa			www.metallica.com
				denmark		wiki.org/metallica

You now SELECT things FROM artist INNER JOIN country INNER JOIN links. First step (the order in which the tables are actually joined together may differ, but the principle remain) yields

metallica		usa
metallica		denmark

And then you join this result with links

metallica		usa		www.metallica.com
metallica		denmark		www.metallica.com
metallica		usa		wiki.org/metallica
metallica		denmark		wiki.org/metallica

Adapt the code in my second example

while ($row = $result->fetch_object()){
	$artists[$row['id']]['artistName'] = $row->artistName;
	$artists[$row['id']]['artistDesc'] = $row->artistDesc;

// Now you always assign the same value to the same key. Thus overwriting the same array element.
$artists[$row['id']]['countries'][$row->countryName] = $row->countryName;
$artists[$row['id']]['links'][$row->artistLinkURL] = $row->artistLinkURL;
}

[knucklehead]

Brilliant!
This works like I expected =)
Thank you very much for help!

Just to ask something about the query itself... You got some result with JOINS. I got same result with SELECT ... FROM ... WHERE ...

Basically, we did get same result, but what is the difference between those 2 queries?
Since there will be lots of information in my database, I would like to optimize every aspect of communication between php and mysql so please give me some info or links about the difference... Thanks 😃

I have some more dilemmas about constructing my classes but I will post it in another topic...

Thank you one more time! 😉

[johanafm]

FROM table1, table2
WHERE table1.id = table2.table1_id

is the same thing as

FROM table1
INNER JOIN table2 ON table1.id = table2.table1_id

The differences are that the joins expect an ON clause (or using), whereas FROM 1, 2 does not require a WHERE clause that compares a field from one table with a field from the other.
So,
SELECT t1., t2.
FROM t1, t2
will produce the cartesian product of those two tables.

There is no speed difference between the two different ways of writing the queries. Put EXPLAIN infront of SELECT to see what happens with either FROM , or FROM .. INNER JOIN to see that they are identical in this aspect.

And join syntax, including t, t2, t3... can be found here for mysql.

[knucklehead]

Thank you very very much for explanation and clearing it out for me 😉