Need help for database structure and indexing to display banner ads

tfe

Hello,

I am implementing a banner ads system on my website. I will allow users to create banner ads with different display options.

A simple mysql query will look like this:

$banner = mysqli_query($connection1,"SELECT select banner_image, url FROM banners WHERE banner_active='1' order by exposures ASC limit 0,1") or die('Error');

So far there is no problem. I create an index on columns banner_active, exposures and that's all.

But the users will be allowed to select provinces where to show their ads and to select genders (male, female, other or all 3) whom to show their ads. There are 50 provinces in Spain and I can add 50 columns to the table but it's not a solution because of the gender option. I can add 3 columns for genders but then I can't create so many indexes.

If I show a banner ad for a male visitor from province of Las Palmas, the query should look like this:

$banner = mysqli_query($connection1,"SELECT select banner_image, url FROM banners WHERE banner_active='1' amd province1='1' and gender1='1' order by exposures ASC limit 0,1") or die('Error');

So, in this example I need an index on columns banner_active, province1, gender1, exposures. But there is 50 provinces and 2 genders. I will need a huge amount (120) of indexes:

banner_active, province1, gender1, exposures
banner_active, province2, gender1, exposures
banner_active, province3, gender1, exposures
banner_active, province4, gender1, exposures
... etc
banner_active, province1, gender2, exposures
banner_active, province2, gender2, exposures
banner_active, province3, gender2, exposures
banner_active, province4, gender2, exposures

I wanted to create a separate table banners_provinces where to insert all selected provinces and then the query could be like this:

$banner = mysqli_query($connection1,"SELECT select banner_id p, banner_image b, url b FROM banners_provinces p join banners b on p.select banner_id=b.id WHERE p.province='$visitor_province' order by p.exposures ASC limit 0,1") or die('Error');

but there is also e gender option and that's makes everything very complicated.

I am sorry for my English but I hope you can understand my problem. Can you give me any suggestions for database structure, and indexing?

Thanks.

Weedpacket

I wanted to create a separate table banners_provinces where to insert all selected provinces and then the query could be like this:

That would be the way to do it: one table for provinces, and a banners_provinces table to link them, which has a record for each province/banner combination which is selected.

Then you'd do the same for gender and for whatever other options you'd select. It wouldn't be very complicated if you wrote your query more clearly:

$banner = mysqli_query($connection1, "SELECT
	b.id,
	b.image,
	b.url
FROM banners b
JOIN banners_provinces bp ON bp.banner_id=b.id
JOIN provinces p          ON bp.province_id=p.id
WHERE
	p.id='$visitor_province'
order by p.exposures ASC limit 0,1");

Like, adding gender it becomes

$banner = mysqli_query($connection1, "SELECT
	b.id,
	b.image,
	b.url
FROM banners b
JOIN banners_provinces bp ON bp.banner_id=b.id
JOIN provinces p          ON bp.province_id=p.id
JOIN banners_genders bg   ON bg.banner_id=b.id
JOIN genders g            ON bg.gender_id=g.id
WHERE
	p.id='$visitor_province'
AND	g.id='$visitor_gender'
order by p.exposures ASC limit 0,1");

Actually, since none of the information in the genders table is used, that table isn't really needed.

$banner = mysqli_query($connection1, "SELECT
	b.id,
	b.image,
	b.url
FROM banners b
JOIN banners_provinces bp ON bp.banner_id=b.id
JOIN provinces p          ON bp.province_id=p.id
JOIN banners_genders bg   ON bg.banner_id=b.id
WHERE
	p.id='$visitor_province'
AND	bg.gender_id='$visitor_gender'
order by p.exposures ASC limit 0,1");

Indexing: Obviously the primary key column of the main tables should be indexed. Also good would be an index on each of the joining tables that covers both columns.

tfe

Weedpacket, thank you!