MySQL - Share column data across two tables

1

I have two tables:

Item purchases:

+----------------------+--------------+------+-----+---------+----------------+
| Field                | Type         | Null | Key | Default | Extra          |
+----------------------+--------------+------+-----+---------+----------------+
| item _name           | varchar(60)  | NO   |     | NULL    |                |
| customer_name        | varchar(60)  | NO   |     | NULL    |                |
| customer_email       | varchar(100) | NO   |     | NULL    |                |
+----------------------+--------------+------+-----+---------+----------------+

Customer details:

+-----------------+---------------+------+-----+---------+-------+
| Field           | Type          | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| first_name      | varchar(60)   | NO   |     | NULL    |       |
| surname         | varchar(60)   | NO   |     | NULL    |       |
| gender          | enum('M','F') | NO   |     | NULL    |       |
| customer_email  | varchar(100)  | NO   |     | NULL    |       |
+-----------------+---------------+------+-----+---------+-------+

Since the data entered for column ‘customer_email’ will always be the same, is there a way to tell MySQL to share the same data for ‘customer_email’ for every table that has this column? I don’t want to have to duplicate the same database queries in my application.

Thanks in advance!

2

I think the better way to do this is to add an id field to the customers table (preferably make that the primary key), then replace the customer_name and customer_email fields with customer_id.

Then whenever you insert a new row to the items table, you refer to the customer row with that id. If you want to “replace” the customer id with actual customer information, you’ll have to use a JOIN, if I recall right (sorry, I haven’t done SQL much in a long time, so that’s as much as I can remember)

3

Reiterating what @kevcomedia said. The purchases table doesn’t need the customer name or email, it just needs a customer_id column that references the id column of the customers table. This should be created as a constraint on the DB. The id column on customers should be generated automatically (it is an index, and whatever you use will have a capability to autogenerate indices), and the customer_id should be a foreign key. Generally the customer id column on purchases is going to be populated during the normal course of the application’s purchasing procedure.

https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

You then run a JOIN when you do a query.

Don’t duplicate the information, this is not a good way to structure the data.

4

A video explaining the basics of relational databases: https://www.youtube.com/watch?v=NvrpuBAMddw