newbie question 1

[webscripter]

Hi all,

Can I create a table within a table?

I just installed mysql on win98 home computer and I have a website on a unix server that I'm going to experiment on. So I want to make my code portable of course. Not sure if this matters.

My question is on structure. Here is the scenario:

I have a website building program that allows the user to add their own templates to generate pages. It allows them to create keywords and overwrites the keyword in the template with the value the user types in the text field. Very simple.

The shopping cart for the program uses this same method of keywords for their items.

I can only use one database name.
Therefore I have to create tables to divide my data.

-database (whole site)
-table (one table each folder of cart items)
-table (for each catagory of items)
-table (for each item)
-columns (for keywords)

(Here I need a table within a table, can do that? I would like to list the keywords and their values but that could make a table with hundreds of columns because the keywords can be different for each item.)

Anyone have good tutorial links with an example?



Thanks
Tricia
yorkeylady@earthlink.net

 

[sleipnir214]

You cannot have a table within a table. You are going to use a related set of tables.

Each table in your hierarchy has a unique record index of its own, and a column to store the index of the record in the table above it in the hierarchy.

SELECT queries can relate records across tables using these common values.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[webscripter]

I'm not sure what you meant by the table above it.
----------------
Each table in your hierarchy has a unique record index of its own, and a column to store the index of the record in the table above it in the hierarchy.
----------------

Thanks
Tricia
yorkeylady@earthlink.net

 

[webscripter]

table1 (whole list of items)

table2 (catagory of items)
col 1 | col 2 |
----------------------------
table1| cat name|

table3 (catagory of items)
col 1 | col 2 | col3
----------------------------
table1| table2 | name of item

How does this help? I want to add an unlimited amount different keywords per item.

Thanks
Tricia
yorkeylady@earthlink.net

 

[sleipnir214]

Instead of the idea of a table within a table, think of tables related to tables.

I think a couple of examples are in order.

Suppose you need to record students and their test scores throughout a school year. One way to organize the database would be to have student and test_scores tables like:

students:                    test_scores:
+------------+---------+     +----+------------+------------+------------+
| student_ID | name    |     | ID | student_ID | test_date  | test_score |
+------------+---------+     +----+------------+------------+------------+
|          1 | Adam    |     |  1 |          1 | 2003-10-01 |         95 |
|          2 | Barbara |     |  2 |          2 | 2003-10-01 |         92 |
|          3 | Charles |     |  3 |          3 | 2003-10-01 |         87 |
|          4 | Darla   |     |  4 |          4 | 2003-10-01 |         90 |
+------------+---------+     |  5 |          1 | 2003-10-15 |         92 |
                             |  6 |          2 | 2003-10-15 |         87 |
                             |  7 |          3 | 2003-10-15 |         90 |
                             |  8 |          4 | 2003-10-15 |         95 |
                             +----+------------+------------+------------+

Every student record has a unique identifier. Every test record has a uniquely identifier and data which records the score, the date, and the student's unique identifier. By matching a student_id in the students table with a student_id in the test_scores table, you can relate the records.

Something like this SELECT query:

SELECT name, test_date, test_score
FROM students, test_scores
WHERE students.student_id = test_scores.student_ID;

will return:

+---------+------------+------------+
| name    | test_date  | test_score |
+---------+------------+------------+
| Adam    | 2003-10-01 |         95 |
| Barbara | 2003-10-01 |         92 |
| Charles | 2003-10-01 |         87 |
| Darla   | 2003-10-01 |         90 |
| Adam    | 2003-10-15 |         92 |
| Barbara | 2003-10-15 |         87 |
| Charles | 2003-10-15 |         90 |
| Darla   | 2003-10-15 |         95 |
+---------+------------+------------+

As a second example, suppose that you are recording your book collection. You maintain records on books and authors. But a single author may have written more than one book, and a book may have more than one author. For this, you can use an intermediate table to record the relationships.

books:                                          authors:
+--------+--------------------------------+     +----------+-------------+
| bookID | name                           |     | authorID | authorname  |
+--------+--------------------------------+     +----------+-------------+
|      1 | How I spent my summer vacation |     |        1 | Adam Berg   |
|      2 | The end of the world           |     |        2 | Claire Dean |
|      3 | Navigating Tek-Tips            |     +----------+-------------+
+--------+--------------------------------+

books2authors:
+--------+----------+
| bookID | authorID |
+--------+----------+
|      1 |        1 |
|      2 |        1 |
|      2 |        2 |
|      3 |        2 |
+--------+----------+

In a query, you can relate the books and author records through the intermediate table, books2authors:

SELECT books.name, authors.authorname
FROM books, books2authors, authors
WHERE books.bookID = books2authors.bookID AND books2authors.authorID = authors.authorID;

which will return:

+--------------------------------+-------------+
| name                           | authorname  |
+--------------------------------+-------------+
| How I spent my summer vacation | Adam Berg   |
| The end of the world           | Adam Berg   |
| The end of the world           | Claire Dean |
| Navigating Tek-Tips            | Claire Dean |
+--------------------------------+-------------+

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!