Best way to store simple social media stats

I'm trying to track really simple social media stats for some companies. At this stage I'm largely storing just their total audience size on different social media platforms.

Is this structure the best way to do this or is there a more efficient/better way?

Stores the base brand infomation

Schema::create('brands', function(Blueprint $table) {
            $table->increments('id');
            $table->string('name')->unique();
            $table->string('slug')->nullable();
            $table->string('url')->unique();
            $table->integer('total_audience')->unsigned();
            $table->timestamps();
            $table->softDeletes();
        });

Stores information about specific instances of accounts on social networks

Schema::create('brandAccounts', function(Blueprint $table) {
            $table->increments('id');
            $table->integer('brand_id')->unsigned();
            $table->foreign('brand_id')->references('id')->on('brands');
            $table->enum('network', array('facebook', 'twitter', 'instagram','youtube'));
            $table->string('network_id');
            $table->timestamps();
            $table->softDeletes();
        });

Stores individual data points for specific social media accounts.

        Schema::create('brandAccountLogs', function(Blueprint $table) {
            $table->increments('id');
            $table->integer('brand_account_id')->unsigned();
            $table->foreign('brand_account_id')->references('id')->on('brandAccounts');
            $table->integer('log')->unsigned();
            $table->timestamps();
            $table->softDeletes();
        });

I should probably put indexes on the foreign keys too.

martinbean

10 years ago

Level 80

@Baadier I did something similar in an application not so long back. I had a table of providers (Facebook, Twitter etc), and a pivot table between users and providers that formed accounts. Each day, a scheduled task would go through all accounts and fetch the number of fans/followers/likes from the corresponding provider; this went into another table called metrics. Having daily data meant I could then create graphs that showed the change over time.

Off-topic, it’s worth following Laravel’s conventions for naming, i.e. using snake_case for table names.

Baadier

10 years ago

Level 2

Thanks for the feedback @martinbean I thought I was following the convention? I remember struggling to get it working for some reason and just explicitly setting the database table in the Model.

Were you using eager loading when you pulled the stats?

  public function getIndexPage(){
        $brands = Brand::with(['brandAccounts','brandAccountLogs'])->orderBy('total_audience', 'DESC')->get();
        return view('social-media-tracker.index')->with('brands', $brands);
    }

Thats what I'm currently doing but it doesnt work correctly and the queries are suffering from N + 1.

On an aside. I ended up setting a cron to total the sum of the different accounts per brand so that I could order by that.

I think I would be able to write a raw sql statement but is their an Eloquent way to return the sum of the columns and order by that?

Please or to participate in this conversation.