-1

I am re-creating a database structure from old tables. New tables should be optimized and easy to understand.

In old database the developer used video table name for storing video blogs. However I don't think it's a proper name for storing video blogs. The below is old table

video:
    _attributes: { phpName: Video }
    ID: { type: INTEGER, size: '11', primaryKey: true, autoIncrement: true, required: true }
    coupon_banner_id: { type: INTEGER, size: '10', foreignTable: coupon_banner, foreignReference: id, onUpdate: CASCADE, onDelete: CASCADE }
    blog: { type: TINYINT, size: '1', required: true, defaultValue: '0' }
    catid: { type: INTEGER, size: '11', required: true, defaultValue: '0' }
    title: { type: VARCHAR, size: '100', required: true, defaultValue: '' }    
    description: { type: LONGVARCHAR, required: true }
    blogtopid: { type: INTEGER, size: '11', required: true, defaultValue: '1' }
    video: { type: VARCHAR, size: '50', required: true, defaultValue: '' }
    vlink: { type: LONGVARCHAR, required: true }
    picture: { type: VARCHAR, size: '255', required: true, defaultValue: '' }
    video_picture_alt: { type: VARCHAR, size: '255', required: false, defaultValue: '' }
    apicture: { type: VARCHAR, size: '150', required: true, defaultValue: '' }
    usid: { type: INTEGER, size: '11', required: true, defaultValue: '0' }
    datein: { type: TIMESTAMP, required: true, defaultValue: '0000-00-00 00:00:00' }
    koview: { type: INTEGER, size: '11', required: true, defaultValue: '0' }
    korating: { type: INTEGER, size: '11', required: true, defaultValue: '0' }
    kototrat: { type: INTEGER, size: '11', required: true, defaultValue: '0' }
    konota: { type: INTEGER, size: '11', required: true, defaultValue: '0' }
    kocomm: { type: INTEGER, size: '11', required: true, defaultValue: '0' }
    kofavorite: { type: INTEGER, size: '11', required: true, defaultValue: '0' }
    kofeatured: { type: INTEGER, size: '11', required: true, defaultValue: '0' }
    koorder: { type: INTEGER, size: '11', required: true, defaultValue: '999' }
    commtime: { type: TIMESTAMP, required: true, defaultValue: '0000-00-00 00:00:00' }
    is_active: { type: BOOLEAN, required: true, defaultValue: '0'}
    slug: { type: VARCHAR, size: '100', required: true, defaultValue: '' }
  video_cat:
    _attributes: { phpName: VideoCat }
    ID: { type: INTEGER, size: '11', primaryKey: true, autoIncrement: true, required: true }
    category: { type: VARCHAR, size: '50', required: true, defaultValue: '' }
    meta_title: { type: VARCHAR, size: '255', required: true, defaultValue: '' }
    category_desc: { type: LONGVARCHAR, required: false, defaultValue: '' }
    meta_desc: { type: LONGVARCHAR, required: false, defaultValue: '' }
    position: { type: INTEGER, size: '3', required: true, defaultValue: '999' }

I renamed it and optimized it as below:

article:
    id,
    category_id,
    user_id,
    title,
    slug,
    description,
    video_link,
    video_description,
    video_thumbnail,
    Video_thumbnail_alt,
    article_description ( I will be using CKEditor here so styles, which data type is better text or longvarchar? ),
    is_active (int or boolean which one is better),
    is_featured (int or boolean which one is better),
    is_favorite (int or boolean which one is better),
    created_at DATETIME,
    updated_at DATETIME

What else do you think needs to be improved in my new optimized table? I also want to know that whether storing video and article in a separate table is better or is it ok to store in the same table as I'm doing?

closed as unclear what you're asking by Akina, MDCCL, mustaccio, McNets, Michael Green Dec 7 '18 at 0:11

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    it all depends on the types of queries you do on both and are they usually considered separate items or usually the same. – danblack Dec 6 '18 at 18:59
  • 1
    Share what challenges you are facing with the existing table / why you are moving to new design or provide more details about the exact requirement. This will help you to get the answer quickly. – rathishDBA Dec 6 '18 at 19:03
  • 1
    You want it "optimized", but what measure do you want maximised with respect to what invariants? If this question is just about naming then we're just giving opinions and prejudices, not defensible recommendations. I'm voting to close this question. – Michael Green Dec 7 '18 at 0:11
0

Separate tables or not -- that depends on

  • Are most of the columns the same?
  • Are most of the operations on the table(s) the same?

If a video can be in multiple categories, you will need a many-to-many mapping table.

What is position, and how will it be maintained?

Not the answer you're looking for? Browse other questions tagged or ask your own question.