Is there a reference field somewhere when you create tables so that you link fields between them? Thanks.
Apr 7, 2001, 10:34 AM
Veteran (17240 posts)
Apr 7, 2001, 10:34 AM
Post #2 of 6
Views: 5128
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
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
Apr 13, 2001, 7:37 AM
Veteran (17240 posts)
Apr 13, 2001, 7:37 AM
Post #4 of 6
Views: 5111
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
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
Apr 19, 2002, 10:58 AM
Novice (18 posts)
Apr 19, 2002, 10:58 AM
Post #5 of 6
Views: 4961
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
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
Last edited by:
jeffpitcher: Apr 19, 2002, 10:59 AM
Apr 19, 2002, 11:53 AM
Veteran (17240 posts)
Apr 19, 2002, 11:53 AM
Post #6 of 6
Views: 4959
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
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