friedemann_bach Posted March 25, 2009 Share Posted March 25, 2009 Hello! I am going to develop a large database that is meant to represent a number of relationships between data objects like persons, locations, events, names, keywords and so on. Possible relationships would exist between persons and locations (P was born in L; P lives in L; P works in L), between persons and events (P organized E, P was a guest at E, P spoke at E) and so on. So each relation can have a different quality. Now, instead of defining each relationship in a separate n:m table, I thought of defining a large n:m table for all types of relationships, including those between persons and events, persons and locations, locations and events, etcetera. Then I just would have to give the names of the two related tables and specify the related ids, and I would have just one table to maintain. Does anybody have experience with this method and could give me any hints? The relationship table would surely become very large and I don't know how efficient this would be. Can you recommend to realize this with MySQL or would you prefer another database system? Quote Link to comment Share on other sites More sharing options...
JonnoTheDev Posted March 25, 2009 Share Posted March 25, 2009 You would have to emphasise more on the database design. If your database is properly normalised and contains the correct indexes on the required fields then it doesn't matter how large the table gets (you can store millions of rows). You must make sure you write the most optimised queries. From your description I picture the db as: person ======= personId name location ======= locationId name personToLocation ============== id personId locationId events ======= eventId name personToEvents ============== id personId eventId and so on Quote Link to comment Share on other sites More sharing options...
friedemann_bach Posted March 25, 2009 Author Share Posted March 25, 2009 Thanks for your response. I can see your argument on indexes. Regarding the design, I thought of something that looks more like this: person ======= personId name location ======= locationId name events ======= eventId name relations ======= id type table1 table2 id1 id2 So I would not need to define each relationship type in a separate n:m table, but I would have one relationship table for all. Would this work? A rough example for a relationship data entry would be: relations ======= id = 1 type = "lives in" table1 = persons table2 = locations id1 = 1 id2 = 1 Quote Link to comment Share on other sites More sharing options...
JonnoTheDev Posted March 25, 2009 Share Posted March 25, 2009 Its possible that this could work however an unorthodox approach. I would change the type, table1, and table2 fields to integers and separate into tables of their own as you are creating duplicate rows of information and this is not normalised so: relations ======= id type table1 table2 id1 id2 tables ======= tableId name types ======= typeId name tables ======= tableId: 1 name: persons tableId: 2 name: locations types ======= typeId: 1 name: lives in relations ======= id: 1 type: 1 table1: 1 table2: 2 id1: 1 id2: 2 With your approach, in your queries you must know the table names that you will be joining the id1 and id2 fields to prior or you will end up doubling up on the number of queries to make to fetch the desired results Quote Link to comment Share on other sites More sharing options...
friedemann_bach Posted March 25, 2009 Author Share Posted March 25, 2009 Thank you. That leads into an interesting direction. Although, as you said, this is completely unorthodox. And I don't like the idea of having to fetch the table names before. I now thought of another model. I don't know whether MySQL is suited for such an approach and I would like to hear your opinion on that. The idea is to define as well object as relation types, so I would not have to look up in another table. object_types ========== id name relation_types =========== id object_type_1_id name object_type_2_id objects ====== id object_type_id name relations ====== id relation_type_id object_1_id object_2_id object_types ========== id = 1 name = person id = 2 name = location relation_types =========== id = 1 object_type_1_id = 1 name = lives_in object_type_2_id = 2 objects ====== id = 1 object_type_id = 1 name = John id = 2 object_type_id = 2 name = Liverpool relations ====== id = 1 relation_type_id = 1 object1_id = 1 object2_id = 2 What I am now trying to figure out is how the query would look like if I wanted to know where John lives. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.