Linking 2 table and create new table

Question

I have 2 tables

Table name : Customer

+----+----------------+--------------+
| ID |Name            | Supervisor   |
+----+----------------+--------------+
| C1 | Jenny          | 2011-Apr-2   |
| C2 | Jack           | 1975-May-8   |
| C3 | May            | 1953-Sept-3  |
+----+----------------+--------------+

Table name: Family

+----+----------------+--------------+-----------+
| ID | Name           | Cust_id      |  Status   |
+----+----------------+--------------+-----------+
| F1 | James          | C1           |  Spouse   |
| F2 | Charlie        | C1           |  Child    |
| F3 | Jane           | C3           |  Child    |
+----+----------------+--------------+-----------+

With these 2 tables, I would like to create a new table which joining 2 tables together. (using CREATE TABLE clause)

Table name : Membership

+---------------+------+-------------+-------------+
| Membership_id | Id   | Name        | Date_created|
+---------------+------+-------------+-------------+
| M1            | C1   | Jenny       |  2017-Dec-5 |
| M2            | F1   | James       |  2018-Jan-2 |
| M3            | F2   | Charlie     |  2018-Jan-15|
| M4            | F3   | Jane        |  2018-Jan-26|
| M5            | C3   | May         |  2018-Feb-4 |
+---------------+--------------------+-------------+

I will use the membership id to book the item. Table name : Book (mem_id is link to Membership_id)

+---------------+----------+-------------+
| Book_id       | mem_id   | Book_item   | 
+---------------+----------+-------------+
| B1            | M2       | Chair       |
| B2            | M3       | Table       |
| B3            | M5       | Table       |
+---------------+----------+-------------+

I'm a total beginner and tried using JOIN and UNION but not able to get the result as I wanted.. Seek for help

You create the Membership table the same way you created Customer and Family. Then, you add foreign keys into the Membership tables to "link the two tables" — ggbranch, Mar 28 '18 at 02:26
Do you mean by setting Id in Membership to foreign key reference from 2 tables? I tried to set Id and foreign to 2 tables but couldn't work.. I tried : CONSTRAINT fk_Membership FOREIGN KEY (Id) REFERENCES Customer(ID) , but how to add link another foreign key to Family Id — new_learner, Mar 28 '18 at 02:36
You seem to be using alphanumeric keys. Instead, you should be using auto-increment integer columns as the unique ID for each row. Also, do you really want to create the 3rd table, or to perform a query showing the results? If you create the table, then you will encounter issues later when rows from the original tables are altered or deleted. — Sloan Thrasher, Mar 28 '18 at 03:08
I really recommend that you read about [normalization](https://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization), it will help you to improve the current design that you have, and why creating the `membership` table looks like you are about to denormalize your design. — KaeL, Mar 28 '18 at 03:45
@sloan thrasher I'm using Oracle 11g as there is no AUTO-INCREMENT in it..that's fine, I will search on the 'auto increment similar code' for oracle 11g. ok, I will try to redesign my db relations..thanks — new_learner, Mar 28 '18 at 06:26

score 1 · Answer 1 · answered Mar 28 '18 at 02:45

Firstly, the tables can be created without any joining or unions. You can do a simple Create Table the same way for Customer and Family. Then, any "links" required will be done so when querying the table.

However, the tricky part comes in if you want there to be some sort of foreign key constraints. You can refer to this link if that is the case:

Linking 2 table and create new table

1 Answers1