Database structure for mobile phone deals

[Techonia2006]

Tweet

Hi

I need some advice on how to to best set out the database structure for storing mobile phone deals from merchant datafeeds.

Ive come to the conclusion that its best to have a seperate table for

Handsets : showing handset name model image etc

and a seperate table for

Tariffs - Nbr of mins, texts, monthlycost etc.

Both the above tables will be manually filled in.

Then a third table will link the two together by handsetid and tariffid as according to the info in the datafeeds.

Is this the best way to do it - As some feeds are incomplete and do not contain all the info about the tariff for example.

Can anyone tell me how many different tariffs are out there. I'm assuming there to be somewhere is the region of 500 or am a way off.....
I know theres only about 250 different phones so that is not a problem.

Any advice is appreciated

thanks

[Dazza30]

This is a realy interesting question, its a bit dissapointing know one has an answer for you.

[pricethat]

In an ideal world you will have the following:

Your own handset table
A merchants product table
A promotional product/free gift table
A services table (talkplans etc) with ids to seperate the networks.

You would then link the merchants record, free gift and talkplans to you own handset record in the database. Mine looks like this though, a little more complicated.

handset table
merchants product table
promotional product/free gift table
services table (talkplans etc) with ids to seperate the networks.

Handsets2merchantsproducts
Handset2promotionalproducts
Handset2services
Handsets2discounts

So effectively mine is the same but i am adding a seperate tables to store the link relationships between the handsets and the merchants products etc, this causes a hell of a lot more coding and is not so efficient but has many benefits that are a little too complicated to explain here.


Just one note that you are not taking into account is that while there might be 300 tarrifs or so they all have different variables, such as 12 month, 18 month and then of course you get 3 months free line rental deals from the networks, not the merchants and visa versa, so it can be a lot more complicated than just adding them as a single product.

Regards

[Itchy]

Mine looks like this though, a little more complicated.

handset table
merchants product table
promotional product/free gift table
services table (talkplans etc) with ids to seperate the networks.

Handsets2merchantsproducts
Handset2promotionalproducts
Handset2services
Handsets2discounts

Our only uses 6 but does all of the above + extra what they are and how they relate to each other i aint gonna tell ya but its good enough to get todays deals : 33072 Should point out these are avaliable deals but if we was to include the out of stock - discontinued deals then the count would be far more ..oh this is also not taking into account the free gift deals or the sim free/payg deals

Rich:9 Networks

nearly gave me a heart attack though what extra ... ohhh thats what ya meant

[Rich]

Obviously you can't give the whole game away to competitors but my setup is similar to how Pricethat described, though without the link tables as all the relationships are one-to-many so there is no real need for them.

My current stats are: - 335 Phones, 193 Tariffs, 24 Makes, 24 Merchants, 17 Free Gifts & 9 Networks. I won't mention the number of deals as I got told off for boasting last time

Those are the active rows, there are more entries in the tables that aren't active, for example phones that are no longer available.

[lowndsy]

Mine has 1 table, 40000 rows!

[Techonia2006]

Hi

Thanks for the advice - Looking at pricethat's post, I'd say thats exactly what I had planned to do. I suppose you have to work around the poor quality of some of the feeds. Obviously this is reflected in the way you design the database structure

Thanks Guys

[Rich]

I've never had a go of linking database tables, is it something you do through MySQL, a MySQL requests or PHP? Probably a daft question for most but something a n00b like me needs to learn.

You do it in the request, like: - SELECT TableA.FieldA,TableA.FieldB,TableB.FieldA,TableB.F ieldB FROM TableA JOIN TableB ON TableA.FieldA=TableB.FieldC
That probably makes no sense but it really is fairly simple once you've done it a few thousand times.

The other thing I have a problem with are tariffs. Are they the same across all mobile phone providers? So for example:
mobiles4everyone nokia 6111
(I pick this example cause mobiles4everyone is by far the best)
Canary 30 tariff appears for a few different providers (e2save x 2, buymobilephones, onestopphoneshop etc.). Is the Canary 30 (and every other Orange tariff) tariff something you would have input yourself, like you would have your own phone image/thumbnail and description?

Yes it is something you would need to enter yourself and it is the same across all providers, mostly!. E.g. Dolphin £35 (18mth) is 500 Mins + 500 Txts, unless you buy it directly from Orange when it's 550 Mins + Unlimited Txts. (this isn't really an issue as the Orange feed has been broken since they relaunched the program)

Should I be offering a combined data feed to affiliates? If so, what would you pay for it?

[Itchy]

this isn't really an issue as the Orange feed has been broken since they relaunched the program

Working fine in our system

[Rich]

Working fine in our system

Good for you! For me, having the contract length missing, half the offers linking to pages where the offers can't be brought and, until recently, the other half linking to pages with the only option to continue the order process was a phone number makes it a pretty pointless feed.

To answer your questions Kungfu: -
Since the offers form Merchant A and Merchant B are the same type of information, and you want to be able to select deals from multiple merchants, then its simpler to put all the deals in one table.

Searching 20-30,000 records should be small fractions of a second. The important thing is to get your indexes right.

[Itchy]

Good for you!

Cheers pats me developer on the back as well

[pricethat]

Is this a programming willy waving competition or something? In the scale of things you are talking about a silly ten pence script that wont change the world so i hardly think bragging about how many offers you can calculate or whether or not you can use an orange feed is really anything worth shouting about itchy eh?

[Itchy]

Is this a programming willy waving competition or something? In the scale of things you are talking about a silly ten pence script that wont change the world so i hardly think bragging about how many offers you can calculate or whether or not you can use an orange feed is really anything worth shouting about itchy eh?

lol shouting whos shouting just pointing out that it infact it does work for those that can make it work

besides check your old posts out i seem to remember a quite a bit of boasting coming from your end something about a great database script that can run anything you throw at it ...... can it do orange proberly not

[pricethat]

And your actual contribution to the thread was?

[Rich]

Lets not start an argument. There's no point really is there? I'm sorry if I've come across as arrogant in any of my posts. While I may have boasted a little about my site in some posts (hey, I'm proud of it) I hope I've only posted when there has been something to contribute to the thread.

All I was trying to say was the the Orange feed needs improving. I only know of one comparison site that includes the Orange deals from the feed, which I now guess is yours Itchy, and that doesn't include the tariffs with issues - i.e. all the Student, Dolphin and Panther 45+55 ones. That's over 60% of the feed scraped.
This is an example of the landing page when a user clicks a link for the KG800 on Racoon 25. This shows the users choice in the 'your selection' box, but the only next step is to choose the tariff again. In the previous version the select buttons wasn't there leaving the user in a dead end. It would be trivial for the feed to link to the next step in the process, but TD/Orange won't make the change.

If more affiliates complained about the data, rather than putting up with the scraps, then maybe Orange would consider fixing it.