-2

I´m designing a MySQL database, and I have three tables: A, B and C. Table A depicts the design of specific rooms of a house and table B references different possibilities for the design of the room (possible scenarios). Table C contains specific elements for design that can be applicable to each possibility:

Table A

ID Name Description
kitchen Kitchen Kitchen design
bathroom Bathroom Bathroom design

Table B

ID Description Reference_to_A
kitchen_1 wooden floor kitchen kitchen
kitchen_2 marble floor kitchen kitchen
bathroom_1 marble floor bathroom bathroom

Table C

ID Description Reference_to_A Reference_to_B
floor_1 marble floor kitchen kitchen_2
floor_2 Wooden floor kitchen, bathroom kitchen_1, bathroom_1

I need advice on how to build this architecture on MySQL, with the following constraints:

  1. Each row of table C can only be associated to table B (by means of the Reference_to_B column) if table C.Reference_to_A = Table B.reference_to_A
  2. I want to know if its possible to have several references to another table in a column, which I can guess its not. If not, I would appreciate another option that is efficient in terms of size of the database.
Filburt
  • 17,626
  • 12
  • 64
  • 115
HRDSL
  • 711
  • 1
  • 5
  • 22

1 Answers1

0

You have one of your references reversed: Table B should also have a foreign key to reference the type of floor it uses.

Table B

ID Description Reference_to_A Reference_to_C
kitchen_1 wooden floor kitchen kitchen floor_1
kitchen_2 marble floor kitchen kitchen floor_2
bathroom_1 marble floor bathroom bathroom floor_2

Then you don't need any foreign key reference in Table C.

Table C

ID Description
floor_1 marble floor
floor_2 Wooden floor

In this case, you don't have to try to put values in a reference column in a given row (in fact, this is not a good idea in a relational database). The multiplicity comes from the multiple rows in Table B.

Filburt
  • 17,626
  • 12
  • 64
  • 115
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thank you very much for your insight. The thing is, in Table B there are several rows from Table C that can apply. For instance, for a specific kitchen design, there could be many elements applied. That´s why I would need several rows from table C to be associated to several rows in table B. Maybe an additional associations table would make sense? – HRDSL May 22 '23 at 14:18
  • That is not shown in your example data. It appears to me that Table B is the association table, representing a many-to-many relationship between room types (Table A) and floor types (Table C). – Bill Karwin May 22 '23 at 14:33