Is there a reference field somewhere when you create tables so that you link fields between them? Thanks.

Use "foreign keys" by selecting Index in the second table.

Example:

TABLE1

Table1ID (Primary - Auto-Increment)
Attribute1
Attribute2
Attribute3
Attribute4

TABLE2

Table2ID (Primary - Auto-Increment)
Table1ID (Index - Not Unique)
Attribute1
Attribute2
Attribute3
Attribute4

Now, it really depends on the "connection" between the tables...for instance, if you want to have only one record in one table associated with one record in another table (1<->1 connection), then you would simply use the PRIMARY key in Table1 as the PRIMARY key in Table2.

Example:

TABLE1

Table1ID (Primary - Auto-Increment)
Attribute1
Attribute2
Attribute3
Attribute4

TABLE2

Table1ID (Primary)
Attribute1
Attribute2
Attribute3
Attribute4

Now, if you want to have MANY records in Table2 associated with each record in Table1 (1->M connection), then you would use the first example I gave.

Now, if you want to have MANY records in Table1 associated with MANY records in Table2, then you should create an intersection table called Table3, which contains a concatenated key of the PRIMARY keys in Table1 and Table2.

Example:

TABLE1

TableID (Primary - Auto-Increment)
Attribute1
Attribute2
Attribute3
Attribute4

TABLE2

Table2ID (Primary - Auto-Increment)
Attribute1
Attribute2
Attribute3
Attribute4

TABLE3

Table1ID (Index - Not Unique)
Table2ID (Index - Not Unique)

You are posing a basic database modelling/design question. You should learn a little bit about the Relational Model used in SQL database designs before venturing forward.


Regards,

Eliot Lee

Thank you. It took me a while to realize that MySQL doesn't use referencing.

Tsute Chen

Uh...not as intuitive as other Database applications (like MS Access, MSSQL, and Oracle). That is one of the downfalls that you can not check referential integrity of relations/tables within MySQL databases. Of course, you could do the following to check relationships between relations/tables:

1) Use MS Access to create your database. (Use the ER diagram to check relationships.) Then export the individual tables as tab delimited text files. Then upload each of the tables to your MySQL tables via the Import option in MySQLMan.

2) If you are using Links SQL v.2.X, then you could copy the codes used to check referential integrity into MySQLMan...would not be an easy hack though, since Links SQL v.2.X uses mod_perl and MySQLMan (stand alone) doesn't, I believe.

3) Take some database tutorials or classes, so that you can easily know how to create tables effectively and efficiently in MySQL.

Regards,

Eliot Lee

Heckler,

Great explanation!

Let me tell you what I am trying to do and then please tell me which scenario is best suited for this.

I currently have a 650+ field database in DBMan. So each newly created record has 650 fields of data in it. Within each record is about 12 "categories" of information (admin, accounting, hardware, etc.) I want to move this over to DBManSQL and split this up into about 12 tables (categories). Now, each category is not necessarily related to any other category. However, when a new record is created under the Admin category, which would contain the record owner/creator information, I want all 11 other tables to become part of this record. In other words, when someone creates (ADD's) a new record, I want all 12 tables in sync with the Admin table's primary key. I hope this description makes sense to you.

Which example out of the 3 you listed would be appropriate for this scenario. Thanks!

Jeff

I would totally re-design your database schema that you are using with DBMAN. 650 fields within one table is a mess in my opinion (especially if most of them are text data types).

I would re-analyze what you have done in the past, and try to think "relational" between your objects/functions in the real world and translate them into appropriate db objects (tables, attributes).

Basically, re-think what your ultimate goals are from the administrative, user, and end-user perspectives.
========================================
Buh Bye!

Cheers,
Me