Hi everyone
I am hoping someone could give me advice on how to best set up my database. I am new to mysql and have a few tutorial books and also have been searching on line however I can't find anything definitive.
The data the db is meant to hold is data from Google Adwords and will consist of the following:
- Date
- Campaign
- Adgroup
- Clicks
- Impressions
- Cost
- Conversions
As far as the datatypes are concerned I am fairly sure I know what to use. Perhaps the only one I'm unsure of is for the cost, whether to use decimal or float? The cost can be in the £thousands.
My more pressing concern is the adgroup. A lot of tutorials I have read talk about setting up a database for customers where there is a first name, last name, address etc and I can follow this no problem. However for the adgroups the structure of them is not quite as straight forward.
They are structured in the following manner: Client-G-S-Campaign-Adgroup Specifics. So for example FER-G-S-GBUK-HIGH. This shows the client name FER, G for Google, S for Search, GBUK as the campaign, and then HIGH for high cost keywords. Now in this campaign there may be a LOW adgroup, (so the same structure but LOW at the end), MID or to complicate things further, MID-P&S (meaning MID cost adgroups, P&S is Peel & Stick where you isolate one well performing keyword).
There are hundreds of these adgroups of varying lengths (and clients etc) and this makes my current database (which houses all the data in one table) inefficient. However I am unsure on the best way to segment the data in order to achieve joins (which I think would be more efficient).
As always, I would be very grateful for someone to point me in the right direction.
Kind regards
Peter
Appreciate advice on Database setup
Moderator: General Moderators
Re: Appreciate advice on Database setup
You can search over internet or goggling the DB structure. I am sure you definitely get the ready DB script.
Re: Appreciate advice on Database setup
Thanks but I have had a look and cannot find anything suitable
Re: Appreciate advice on Database setup
Peter, you are wise to ask these questions now, before you proceed very far with your database; it will save you much trouble later. Relational databases operate as efficiently and flexibly as they do ONLY because they are based on strict mathematical set theory, first developed by an IBM mathematician, Dr. E. F. Codd around 1970. There are formal rules you can apply to reach the correct way to structure your tables; they are called the "Normal Forms", beginning with First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF). There are a couple more Normal Forms, but if your data meets the first 3 Normal Forms, you will be assured of a solid structure for nearly every purpose. There are many good references online if you search for database normal form. One of the requirements to meet 1NF is that every column (field) should be "atomic" or single-valued; that is, it should not be possible to divide the value into several components. That's the reason you should not ordinarily store first and last names in the same field. It makes it awkward or impossible to do searches on components of a field, for example. So, in your case, I would expect that you should not store Adgroup in one field. That appears to be a cluster of several quite different pieces of data. The exception would be if this is used solely as a unique identifier, and not as separate items of data.
A relational database is a representation or model of something in the real world. The best way to approach data structure decisions is to first identify the entities that will be represented in your model, then identify the relationships that exist among those entities, then identify the attributes of each entity that will be stored. Easier said than done, but it's the only methodical approach that will lead to a clean design. So there's another search you can do for help: data modeling.
Good luck.
A relational database is a representation or model of something in the real world. The best way to approach data structure decisions is to first identify the entities that will be represented in your model, then identify the relationships that exist among those entities, then identify the attributes of each entity that will be stored. Easier said than done, but it's the only methodical approach that will lead to a clean design. So there's another search you can do for help: data modeling.
Good luck.