0

Hi I know that this question has been asked before but I fail to find an efficient way to solve my problem. I have a table that has 10 columns accepting INT that is referring to 10 other tables. My problem is that now I require to have in just one column, two Integers that are both referring to the same table. How is this possible? I thought of having another table to save one row per column and have 10 rows per row of the first table and in this way I can have it linked with that first table's ID but now a new problem.. I can't know which row references which table since in this way it becomes dynamic. Is there any solution to this?

Edit: Just thought of creating 10 new tables all linking to the first table which in turn references everything. Is that really the only way? This will leave me with 21 tables but it will work. Is this good for performance?

itsundefined
  • 1,409
  • 2
  • 12
  • 32
  • 1
    That's extremely vague. I have designed hundreds of databases and I have never had a situation where i had one table with 10 foreign key relationships to it. I doubt seriously your current design makes sense. Certainly adding 10 new tables isn't going to solve or improve anything. There is no issue with one table relating to another table two or more times. – gview Jul 09 '17 at 20:39
  • The main table is essentially a shopping cart having specific items that all together make up for a combo. Every item ID is on a different table since it's from a different category :/ – itsundefined Jul 09 '17 at 20:42
  • @gview, you never saw a [star schema](https://en.wikipedia.org/wiki/Star_schema)? – Bill Karwin Jul 09 '17 at 20:43
  • @BillKarwin this isn't a DW application obviously. – gview Jul 09 '17 at 20:49
  • @slowdeath007 Yes thanks, that is completely the wrong design. Why would you have a separate table for each item, just because it's part of a category? There should be 1 item table. – gview Jul 09 '17 at 20:51
  • Each category has its own columns for the different specifications. Sorry guys I am new to mysql and have only worked with NOSQL before. – itsundefined Jul 09 '17 at 20:53
  • I'm not sure what to tell you here. Do you want to let the tail wag the dog and triple down on mistakes you made with your design. If so, your application is most likely doomed from the start. If you are willing to start over, people can probably help you along, but you have to learn something about normalization. These issues you bring up all have simple oft used solutions. To start with, however, you need to start with tables for your basic entities, and not look at tables like they are intermediary storage for your procedural code. – gview Jul 09 '17 at 20:59
  • I have just started the design of the software. Everything in here can is hypothetical since I just started creating the schema and fell into this problem. – itsundefined Jul 09 '17 at 21:01
  • 1
    I saw you tagged this question [tag:relation]. Please don't fall into the common mistake of saying "relation" when you mean "relationship." A relation is simply a table that follows certain rules. Read https://en.wikipedia.org/wiki/Relation_(database) – Bill Karwin Jul 09 '17 at 21:16
  • @BillKarwin That makes sense. Thank you so much for your time. – itsundefined Jul 09 '17 at 21:18

3 Answers3

3

It sounds like you are struggling to turn a one-to-many relationship into a many-to-many relationship.

You currently have each table referenced by one integer in a row of the main table A.

enter image description here

Now you want to have one or more of these tables referenced by multiple integers associated with a given row in the main table. This is a many-to-many relationship. It requires another table to represent this relationship. This extra table is called an associative entity.

Here's what it would look like for the table K:

enter image description here

Sorry, there's no shortcut for many-to-many relationships. You need the intermediary table. Then you can have multiple rows in AK that reference the given row in A and multiple rows in K.

If all of the relationships are many-to-many, then you need one extra table per relationship.

Other comments are making a good point that your design might not require so many referenced tables. That may be true, since you haven't provided much detail about what these tables are for.

Nevertheless, it's true that every many-to-many relationship requires an associative entity in relational databases.

Also read my answer for How to model a database with many m:n relations on a table.

In the comments it became clear that you are trying to solve a common problem in relational databases, that of describing the attributes of many different product categories. It's always a bit awkward.

I wrote an answer here: How to design a product table for many kinds of product where each product has many parameters

I did a presentation about data modeling here: Practical Object Oriented Models In Sql

And I wrote a chapter in my book about this problem: SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

You might also like to look at the JSON data type in MySQL, which sort of bridges the gap between relational and NoSQL.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Best explanation I've seen. Graphs make it so much better. Thank you very much for your info. I was just skeptical of having badly performing queries since I will need to join all 10 tables. Maybe since this ints are UNIQUE and I can have them indexed it won't be that bad? – itsundefined Jul 09 '17 at 21:04
  • What program did you use to create these graphs in the images? – Noah Cristino Jul 09 '17 at 21:06
  • Performance is a totally separate matter from normalization. Normalization is meant to ensure your data is represented in a "foolproof" manner, so you won't get data anomalies. Indexing depends on what queries you need to run, and you haven't provided any information about that. But you can be assured that foreign keys and primary keys are always implicitly indexed. – Bill Karwin Jul 09 '17 at 21:06
  • @NoahCristino, MySQL Workbench. – Bill Karwin Jul 09 '17 at 21:06
  • 1
    After some more planning I can see that only 3 of the 10 tables will ever need many to many records so a big thumbs up for your answer. – itsundefined Jul 09 '17 at 21:08
  • I considered the exact answer Bill provided to you, but after querying you on the specifics, it became very evident that this idea, while it may work, is completely wrongheaded and adding window dressing to a terrible idea. The fact that people commenting on and impressed by an ERD, says a lot about the audience for this. – gview Jul 10 '17 at 03:41
  • Now I come across Bill on the regular at SO, and in fact he wrote a book on SQL Anti patterns, i.e., what not to do with db design, so I find it a bit ironic that I tried to press you on the basics of your design, have you admit that you did something really bad, like creating essentially numerous product tables per category, for some reason that has no validity, and yet you are excited to gloss this all over and accept an answer based on the fact that you can relate a bunch of tables together essentially however you want even if there is no good reason for doing so, and many reasons not to. – gview Jul 10 '17 at 03:45
  • @gview, I am only answering specifically about the proper way to store many-to-many attributes. I did say in my answer that the overall design might be bad, but I can't make a definite conclusion because the OP hasn't provided enough information. – Bill Karwin Jul 10 '17 at 03:52
  • @gview, Answering specific coding questions with criticism and "you shouldn't do it that way" without offering a constructive alternative is what gives the Stack Overflow community a reputation for being rude and condescending. – Bill Karwin Jul 10 '17 at 03:54
  • When we discussed it: your comment was: "I have just started the design of the software. Everything in here can is hypothetical since I just started creating the schema and fell into this problem." I don't get points for advising you in comments that what you are doing is completely wrong-headed, and if all I cared about was doing that I could have whipped out the same suggestion Bob did, even though you made it clear there was no reason to go down this path in the first place. You should go back to the drawing board on your DB design, and start with a single product table. – gview Jul 10 '17 at 03:58
  • @BillKarwin Which is why I didn't provide an answer, but attempted to solicit additional information from the comments. At that point you jumped in and provided your answer, which from a technical standpoint is exactly how I could have answered but chose not to. No offense is intended to you, and I would appreciate the same courtesy, as my choice was to strongly suggest that the poster start over fresh and not back himself into a corner as he clearly has with a nonsensical design. – gview Jul 10 '17 at 04:01
  • Everything apart from the primary ID of the product has different columns. One product has wattage, max pressure, operating temperature and one other product has speed, dimensions, battery capacity. Every e-shop I've seen has categories on the left for example select only from this manufacturer and they change with each product. Does this information help understand better how a correct schema needs to be created? I am not building an exact e-commerce site and that's why I am not using a CMS but more like a wishlist manager. – itsundefined Jul 10 '17 at 09:41
  • The only way for me to have them in the same table is having around 50 columns and based on what product type I have, around 40 of these columns will be null. Is this a better schema design? – itsundefined Jul 10 '17 at 09:52
1

"I require to have in just one column, two Integers that are both referring to the same table" - this is bad database design.Why not have two columns in the table 1 referencing column A and B of table 2 or alternatively make an independent table 3 and add foreign key constraints liking tables 1 and 2 through it.

phang
  • 518
  • 4
  • 20
  • The first suggestion would work if I knew that it would be at most 2 integers in one column but it could be more. The second suggestion would essentially require 21 tables? Since I will require a third table for each column. – itsundefined Jul 09 '17 at 20:44
  • The thing to avoid is storing things in a column in an array format. Your database will hard to maintain and work with. I would go for the 21 tables since they will be easy to manipulate during transactions. – phang Jul 09 '17 at 20:54
  • That is what I have first in mind. I really don't want to merge these 10 tables since they have somewhat different columns and it helps with statistics collection. – itsundefined Jul 09 '17 at 20:56
  • 1
    Yea...just make sure you do proper constraints and everything will be cool – phang Jul 09 '17 at 21:03
1

Based on my comments, and discussion with Bill, I decided at the risk of wasting more of my time, to go ahead and provide some food for thought.

Your original question was short on details, but you did provide a few nuggets of information. Apparently this is some sort of traditional product/cart/order system. You also stated that for some reason, you made separate product tables for every category, which is the very thing that lead you to this convoluted question.

With database design the devil is in the details. While this took me all of about one minute to create, it is certainly a starting point towards something closer to standard and appropriate.

There may be things we don't understand that necessitate changes or additions to this, but without an articulation of your requirements, thought processes etc, there is no way to incorporate them. Consider this then a straw man, but at least one that an experienced db architect would come up with.

Simple DB

Now I'm sure there is something important having to do with products and categories that this doesn't address, but that would be the topic for a separate and probably more worthwhile discussion.

And certainly a system like this can blow up into a schema with many more tables, but that depends on specific details.

A Part of a much bigger ERD

gview
  • 14,876
  • 3
  • 46
  • 51
  • The reason the products are split is because they are taken from an API that have different specifications. A non electric item does not have the power listed in one of its columns. The first schema you created is completely understandable but is not feasible unless I use a TEXT for the specifications which will make it really hard to later add functionality for specific search like all items that have a specific feature. I really value your time for showing me how an e-commerce websites schema can look at the end. – itsundefined Jul 10 '17 at 09:31
  • 1
    There are many ways to handle the situation you describe. One way is to have a subtype table that relates to Product and includes the type/category. In that table you include all the attributes specific only to that type. So you might call it Product_Electric. Another option is to have a product attributes table. You can have a list of attributes in a lookup table, and for those non-global attributes you have a row for each value, as if they were name=value pairs. Last but not least, you can have a bunch of columns that are NULL. It's not pretty but people opt for that frequently. – gview Jul 10 '17 at 21:12
  • This last comment should have been the "second" accepted answer. Thank you so much for your time. It helped so much. This clearly looks the best way. Time to ditch some 18 tables. – itsundefined Jul 10 '17 at 23:00