Table structure question

Databases
1

OK, I’ve built a stats table to record what pages were looked at in my site.

I want now, to store the keywords used in a search engine. And I need to know when the keywords were used, so that is why the FK to statistics table, which contains the date.

so isn’t this the correct table structure for the keywords?

The other option I was considering was to increment a column for frequency rather than storing each keyword in a separate record. But if I do that, I don’t think I can store the date when the keyword was used, either using the date of the site visit or in this keywords_used table.

dat right?


create table used_keywords
( id bigint not null
, stat_id bigint not null
, business_id bigint not null 
, keyword varchar(99) not null
, constraint keywords_stats_fk
    foreign key (keywords_stat_id)
      references statistcis(id)
) engine....

bazz

2

you’ll be fine as long as you 1. log every keyword reference separately, and 2. increase the size of the keyword column to handle reasonably long phrases

oh, and by the way, change BIGINT to INTEGER UNSIGNED

reason? because by the time you hit 4 billion rows (the limit for INTEGER UNSIGNED) you will need to re-address the entire schema anyway (e.g. for partitioning)

no sense in having all your id columns twice as big (8 bytes) as they need to be

3

Thanks again rudy,

I’ll do as you suggest :wink:

bazz