Trying to maintain normalisation whilst adding to db

Databases
1

Hi,

I have the table structure as shown in the first table, below. I want to add multi-language functionality and I wonder whether I should split the table into two new ones. The relationship seems to me to be 1:1 between english and any other language but I want to ensure that sequence number, live_from, live_to, applies similarly/equally to a specific file, whether it be in French/German/English or whatever language.

What would you recommend?

existing table:


CREATE TABLE files (
  business_id int(11) NOT NULL
, content_category varchar(64) collate utf8_unicode_ci NOT NULL
, file_name varchar(32) collate utf8_unicode_ci NOT NULL
, file_text text collate utf8_unicode_ci NOT NULL
, live_from date NOT NULL default '0000-00-00'
, live_to date NOT NULL default '0000-00-00'
, sequence_number tinyint(4) default NULL
, last_updated timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
, last_updated_by varchar(99) collate utf8_unicode_ci NOT NULL
, PRIMARY KEY  (business_id,content_category,file_name,live_from)
, KEY content_category_fk (content_category)
, KEY business_id (business_id,live_from)
, KEY files_file_ix (business_id,content_category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

table1 of new structure


CREATE TABLE files (
  id int not null auto_increment primary key
, business_id int(11) NOT NULL
, content_category varchar(64) collate utf8_unicode_ci NOT NULL
, live_from date NOT NULL default '0000-00-00'
, live_to date NOT NULL default '0000-00-00'
, sequence_number tinyint(4) default NULL
, unique key (business_id, content_category, live_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

table2 of new structure


CREATE TABLE files_text (
  id int not null auto_increment
, file_id int not null auto_increment 
, language varchar(99) not null
, file_name varchar(32) collate utf8_unicode_ci NOT NULL
, file_text text collate utf8_unicode_ci NOT NULL
, last_updated timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
, last_updated_by varchar(99) collate utf8_unicode_ci NOT NULL
, constraint file_text_file_fk
    foreign key (file_id)
      references files(id) on delete cascade 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

bazz

2

I don’t know what these colums contain, so I couldn’t tell you if those values would be the same in different languages.

Maybe you could explain the meaning of each column, and also give some data examples?