RDBMS, foreign key merged or separate table?

I have an ai_order table.

CREATE TABLE `ai_order`(
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `product_id` INT(11) NOT NULL,
    `quantity` INT(11) NOT NULL,
    `number` VARCHAR(20) NOT NULL,
    `addDate` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `orderProduct`(`product_id`,`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

As an order from a client contains many product with quantity which means multiple rows being inserted for a single order, for example an order contains a 50 product being resulted in creation of 50 rows in ai_order table. now to to identify an individual order i have given a number field which will hold the unique order number (serial) for a single order i.e it will group order_id by number field. below is the table demonstrating how the data is going to be stored in the table.

There will be many tables which will be extending from this parent table. while everyt开发者_开发知识库hing is correct the problem i see is number column repeating the value again and again, considering i have 200 product to be listed the for one order i will have to repeat the number column value again and again in all 200 rows which gives me somewhat ugly feeling.

Now the method to solve this crisis is by storing the order number in a different table.(although repetition cannot be avoided, but not a problem). for example how about removing the number column from the ai_order table and storing it this way.

CREATE TABLE `ai_order_number`(
    `order_id` INT(11) NOT NULL,
    `number` VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

i would like your take on how to deal with this approach? which one would you go with. the first or the later one? any suggestions on improvement will be appreciated.

thank you.

I don't think it will be a good idea to keep a separate table as you suggested as order_number is directly related to order.

If you are still planning to keep a different table, take 'add_date' along with number. I'm finding 'add_date' more uglier than order number.

In case exact timing of adding order is not useful, which is generally the case, you can keep a separate table which will have customer_id, order_number(primary key), order_date

I would go with the first approach since there's nothing wrong with repeating the order number in that table and therefore there's no need to create a second table that will have the order number repeated anyway.

What I would do instead, is perhaps getting rid off the id column and make number the primary key of the table since you are more likely to perform searches, joins, etc, by the order number than by the id column. The number column could be automatically generated as well so that won't be an issue.