table structure problem

[kittyyo]

Hi folks!

I've got the following DB structure question:

Suppose the two tables MasterTable and ChildTable this way:

CREATE TABLE MasterTable
( M_ID INT UNSIGNED NOT NULL,
....
PRIMARY KEY (M_ID)
);

CREATE TABLE ChildTable
( C_ID INT UNSIGNED NOT NULL,
M_ID INT UNSIGNED NOT NULL,
A1 INT UNSIGNED,
A1_TEXT VARCHAR(50),
...
PRIMARY KEY (C_ID),
INDEX (M_ID),
FOREIGN KEY (M_ID)
REFERENCES MasterTable(M_ID)
ON DELETE CASCADE ON UPDATE CASCADE
);


Now, A1_TEXT depends on A1 and on M_ID.
(For same A1 and M_ID, A1_Text has always the same value).

Of course I could be saving disk space if I created another table containing the A1_TEXT values, and, in some way, the M_ID and A1 values.

How would this table look like? What would be the primary and foreign keys to MasterTable and ChildTable?

I'm using InnoDB Tables in MySQL.

Thanks for any answer,
Anne.