Getting related data....
Moderator: General Moderators
- kendall
- Forum Regular
- Posts: 852
- Joined: Tue Jul 30, 2002 10:21 am
- Location: Trinidad, West Indies
- Contact:
Getting related data....
Hi guys,
I have often went on websites and saw instances where you are viewing an "item" (product or non product) and there is a "related" information listing whether it be "links" "similar products" "artists" etc.
I am building a very complex database and I was wondering how is it the this "related" information is "stored"....
1) is it that the relationship is stored as id in a column in the same table the main item is in?
2) is it by using a "keywords" policy thus every record item should be "tagged" in order to create this "related" relationship...
3) or is it that a separate "table" is created in which the relationship is recorded as a record item using thier ids....
As i have stated i am building a very complex database in which their are already foreign keys in tables (but its more of a parent child relationship and not "related data" scenario)...
I myself am contemplating using keywords but I am have a HUGE amount of resistance from my client as this keyword thing can prove to be "inaccurate" and they need it to be "precise"....*sigh* the price of prefectionists
My question really here is what is the best method of approach and how should i determine what to use?
I have often went on websites and saw instances where you are viewing an "item" (product or non product) and there is a "related" information listing whether it be "links" "similar products" "artists" etc.
I am building a very complex database and I was wondering how is it the this "related" information is "stored"....
1) is it that the relationship is stored as id in a column in the same table the main item is in?
2) is it by using a "keywords" policy thus every record item should be "tagged" in order to create this "related" relationship...
3) or is it that a separate "table" is created in which the relationship is recorded as a record item using thier ids....
As i have stated i am building a very complex database in which their are already foreign keys in tables (but its more of a parent child relationship and not "related data" scenario)...
I myself am contemplating using keywords but I am have a HUGE amount of resistance from my client as this keyword thing can prove to be "inaccurate" and they need it to be "precise"....*sigh* the price of prefectionists
My question really here is what is the best method of approach and how should i determine what to use?
Re: Getting related data....
I would certainly use a related table. Relational databases do exactly what I think you described, a fixed relationship between 2 tables, based on a Primary key in one table and Foreign keys in the other. So it sounds like your client is saying that they want a specific, fixed relationship, rather than rely on keywords, which users might or might not think to use.
- kendall
- Forum Regular
- Posts: 852
- Joined: Tue Jul 30, 2002 10:21 am
- Location: Trinidad, West Indies
- Contact:
Re: Getting related data....
Ok
so i have 2 tables....table A and table B....and i create a table C that will hold information that may indicate the relationship between table A and table B....and also maybe a relationship than can be table B and table A...
the primary keys of both tables are int auto increments so in addition to the ids of both table i need to store the table names...
but is there another scenario that can be used in this instance? maybe some standard pattern arrangement to create the record in table C without having to use a column to record the table names?
what should i have done?
Kendall
so i have 2 tables....table A and table B....and i create a table C that will hold information that may indicate the relationship between table A and table B....and also maybe a relationship than can be table B and table A...
the primary keys of both tables are int auto increments so in addition to the ids of both table i need to store the table names...
but is there another scenario that can be used in this instance? maybe some standard pattern arrangement to create the record in table C without having to use a column to record the table names?
what should i have done?
Kendall
Re: Getting related data....
No, there's no need to store table names. The relationship is established in your queries.kendall wrote:Ok
so i have 2 tables....table A and table B....and i create a table C that will hold information that may indicate the relationship between table A and table B....and also maybe a relationship than can be table B and table A...
the primary keys of both tables are int auto increments so in addition to the ids of both table i need to store the table names...
Going back to your previous post, you made a distinction between "parent/child" tables and "related" tables, but that is incorrect. They are the exact same thing. All relational tables are simply pairs of tables where at least one table has a unique primary key column (cannot have duplicate rows) and the other table has a foreign key column that may have one or more rows with a key that matches the primary key in the other table. That's the whole mechanism. Actually, it's a lot simpler than some people seem to think it is.but is there another scenario that can be used in this instance? maybe some standard pattern arrangement to create the record in table C without having to use a column to record the table names?
Kendall
There are a couple of ways to join such tables in a query. You can use the JOIN syntax of SQL, or you can use the WHERE clause, setting up the equality requirements (like "WHERE a.id = b.id"). There are subtle differences in how the database engine extracts the desired records (and I'm not an expert in the internals of database engines) and so sometimes one of those ways may be more efficient or more flexible than the other, but the way the tables are structured is exactly the same in all cases.
Hope that helps your understanding of the underlying principles.
- kendall
- Forum Regular
- Posts: 852
- Joined: Tue Jul 30, 2002 10:21 am
- Location: Trinidad, West Indies
- Contact:
Re: Getting related data....
I think i may be mis leading you...while i have parent/child tables.....the related data's table does not have anything to do with the parent child relationship but more "table" information thus....Table 1 and Table A are a parent child relationship....but Table B and Table A are not....hence why i need to create this "relationship table"...as Table B and Table A may have instances where there MAY have related data....not only this...but because of this...I now need to consider that other tables may have "unexpected" relationships that i CANNOT cater for in their existing design...hence again...the readon why i am considering a "related tables".Going back to your previous post, you made a distinction between "parent/child" tables and "related" tables, but that is incorrect. They are the exact same thing.
Further to this...I have the concern when i need to "delete" information which MAY have not only parent child relationships but "related" relationships.
Now ....i thought i could have only used id's but because of the fact that i may have related data amongst more than one table i came up with the table design
Code: Select all
CREATE TABLE `relatedinfo_table` (
`rid` int(10) UNSIGNED AUTO_INCREMENT NOT NULL,
`refid` int NOT NULL DEFAULT '0',
`relid` int NOT NULL DEFAULT '0',
`reftable` varchar(20) CHARACTER SET `utf8` COLLATE `utf8_general_ci` NOT NULL,
`reltable` varchar(20) CHARACTER SET `utf8` COLLATE `utf8_general_ci` NOT NULL,
/* Keys */
PRIMARY KEY (`rid`)
) ENGINE = MyISAM;
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: Getting related data....
Like califdon pointed out, you should not need to store the table name relationship in the relationship table, and should be defined in the query itself.
What I would suggest doing, since you have products across multiple tables, is creating a new table that stores a universal product id. Therefore, you can reference products uniquely across all tables using this column instead of the primary key id. When new products are inserted it would require creating a new record in this table and retrieving it's id though (a little extra work).
where ref_id is the product you want to check it's relationship towards.
What I would suggest doing, since you have products across multiple tables, is creating a new table that stores a universal product id. Therefore, you can reference products uniquely across all tables using this column instead of the primary key id. When new products are inserted it would require creating a new record in this table and retrieving it's id though (a little extra work).
Code: Select all
SELECT * FROM `product_relationship`
LEFT JOIN `product_table2` ON `product_table2`.`product_id` = `relationship_table`.`rel_id`
LEFT JOIN `product_table3` ON `product_table3`.`product_id` = `relationship_table`.`rel_id`
LEFT JOIN `product_table4` ON `product_table4`.`product_id` = `relationship_table`.`rel_id`
WHERE ref_id = 4- kendall
- Forum Regular
- Posts: 852
- Joined: Tue Jul 30, 2002 10:21 am
- Location: Trinidad, West Indies
- Contact:
Re: Getting related data....
That would be good except
there is a product_id = 2 in Table B
and a product_id = 2 in Table A
and even a product_id = 2 in a Table F
(not dealing with products but with "historic" information)
i used auto increment ids...
(too late to turn that around now)
thus there would be a TABLE B related to TABLE A, TABLE B related to TABLE F TABLE F related to TABLE A...TABLE B related to TABLE A etc etc....
NOW do you see my delima...
there is a product_id = 2 in Table B
and a product_id = 2 in Table A
and even a product_id = 2 in a Table F
(not dealing with products but with "historic" information)
i used auto increment ids...
thus there would be a TABLE B related to TABLE A, TABLE B related to TABLE F TABLE F related to TABLE A...TABLE B related to TABLE A etc etc....
NOW do you see my delima...
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: Getting related data....
You still need auto incremented id's for each table, what I suggested is adding a product_id column.