gigantorTRON Posted March 6, 2008 Share Posted March 6, 2008 Obviously the answer to this question is heavily dependent upon the situation, but, generally, is it better to create separate tables and use JOIN syntax to join them and pull data, or is it better to use an 'association' table that contains ID associations? Or, is there an even better alternative that I'm not aware of? Thanks! Quote Link to comment Share on other sites More sharing options...
aschk Posted March 6, 2008 Share Posted March 6, 2008 Bit difficult to visual exactly what you mean by "association" tables, but taking a guess I would say you mean a table that contains ids from lots of others tables and does some magic to work out which table to use... Quote Link to comment Share on other sites More sharing options...
gigantorTRON Posted March 6, 2008 Author Share Posted March 6, 2008 Yeah. What I'm referring to would be a table that associates PKs between tables. This would allow you to lookup data across tables by first checking the associations table, pulling the needed primary key and then using that to search another table rather than doing multiple joins. I guess it would be handy for complex DB and speed up performance on SELECT queries? Quote Link to comment Share on other sites More sharing options...
Barand Posted March 6, 2008 Share Posted March 6, 2008 Let's take a common example. You are a company and a customer places several orders. Each order has just the one customer but each customer can have many orders. You have, therefore, a simple 1-to-many relationship between customer and order and you just need ro put the custID in order to associate it with its custoner. [pre] customer order ========== =========== custID ---------< custID cust_name ordernum etc orderdate [/pre] With the orders it now gets a little more complicated. Each order can be for sevaral products and each product can be ordered on several diffferent order. You now have a many-to-many relationship between order and product. To handle this you need an "association" table (order_item) [pre] order order_item product =========== =========== =========== custID +----< ordernum +------ prodID ordernum ---+ prodID >------+ description orderdate quantity price etc [/pre] Whether you have the simple cust-order relationship or the more complex order-item-product relationship, you still need JOINs to pull the data using SQL queries 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.