Help me with DB design

Question

Hi, i'm developing text ads system. Some small clone of Google Ads

Updated image so all field names would be seen if you don't seen diagram, click here

Here is diagram with common tables. Basically make it short, advertiser can have up to 10 variant of same campaign with different text variations, can geo-target his ads and unique impressions count only for IP that haven't been on certain site for more than 24 hours.

Pretty simple but the question is what i lack in here from your experience because later it would much harder to fix design flaws and some of you probably done something alike also many SQL gurus in here so maybe i did over normalized DB or did not normalized as needed ?

Second question is. My end goal is to get ads for user from ie. Germany that haven't seen same ad on same site for 24 hours as long as ads fit country of user. Each impression is count same as each click if there is one. I need to get 5 "random" ads based on IP, Country and higher CPC (pay per click). How can i achieve this with current design or maybe to design database the way it would be easy to get ads and show stats for advirtisers...

thanks for any help...

Answer 1

It is better that the name of all Primary keys be ID and in foreign key column have a name like PrimaryTable+ID.

for example the primary key name in Users table must be ID and the related foreign key to it in TextCampaigns table must be UserID.

this is only my opinion.

Answer 2

Overall it looks good, there are a few minor changes I'd make if it were me:

I notice you have Region ID on your Countries table, but there is no Region table. If there is no Region table what prevents incorrect (Negative?) Region IDs from being entered?

Similarly, Campaign Status on the Text Campaigns table is a Tinyint. Is this an Enum being stored? If so, consider a status table as this will prevent statuses being entered that do not exist in your Enum, which can crash your code as try to convert them as you read them back in.

Same again with User Status on the Users table.

Country ID on the Countries table is an Integer, a TinyInt will be enough.

You have budget figures as integers, so is it impossible to budget to the penny/cent/etc, or are these measured in minor sections?

It looks like you're planning to store passwords as plain text? Consider salting and hashing the passwords, then storing just these - Or are you planning to use transparent encryption.

Depending on how long you plan for this to run, VARCHAR(15) will not be enough to store an IP address in IPv6.

If you have SQL 2008, you could use the DATE type on the Text Campaign Daily Stats table, then make Date and Campaign Text Variant ID into the Primary Key.

You could add some fields to the Text Campaign Variant table, like Date From and Date To, so people can set up campaigns in advance. Similarly, Time Start and Time Stop could allow campaigns to be shown at certain times of day.

I might change the names, so Text Campaign is just Campaign and so on - You may want to do video or image campaigns in the future?

There are other things, but this is plenty for now. I would wonder if you have available estimated usage? Might help with identifying more to optimise.