SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    table structure question

    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?

    Code MySQL:
    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
    Last edited by IBazz; Feb 6, 2011 at 19:54. Reason: added question mark so it wasn;t seen as a rhetorical question.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again rudy,

    I'll do as you suggest

    bazz


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •