0

I would like to create a common table in mysql (called e.g. repair) for two different tables (e.g. sportscar and tank). Both need to be repaired and here comes my problem. I can't have only the id (PK) of the two tables sportscar and tank in table repair, because I can't decide for ID 1 if it's from sportscar or from tank. So I have added type_id also to table repair. So vehicle_id = 1 and type_id = 2 should define that it's from table tank, and vehicle Id is 1. So far so good, but unfortunately it's not working. I have no clue why, but it seems not to be accepting somehow vehicle_id and type_id as a composite key. In SqlYog I'm not allowed to choose a corresponding record, because it's always empty in the related tables. I was trying a lot of different combinations, even to set id and type_id as composite PK in tables sportscar and tank, but didn't work. What am I doing wrong? Can you please point me to the right direction? Thank you. enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

user2511599
  • 796
  • 1
  • 13
  • 38
  • The repair table should have the FK from CAR not the specialized tables. And the specialized tables should also have an FK to CAR, since the relation is 1 to 1 from car to specialized, the car registry is also a specialized. You can keep the type table, although not really necessary. – Jorge Campos Jul 25 '18 at 11:54
  • `car (id, type_id, someAttr); sportscar (id_fk_toCar, otherAttr); hothatch (id_fk_toCar, otherAttr); repair( id, id_fk_to_car, otherAttr)` – Jorge Campos Jul 25 '18 at 11:56
  • No, the specialized tables will only store specialized attributes, think of it like all attrs from sportscar and hothatch where in car table, and you only fill the ones that car is. And to query it you would simple do a left join on both to car. the id_fk_to_car in both specialized should also be the PK in both tables. – Jorge Campos Jul 25 '18 at 12:06
  • if you use my suggestion, the car itself is already the specialized. so to have the data, you join repair with car and left join with sportscar and with hothatch, the table that has the id not null is your type (that's why you don't really need type) – Jorge Campos Jul 25 '18 at 12:08
  • The only way to do it (since you removed the parent table e.g. car) is to add two fields in repair with constraints for both tables. Then you would control it through sql. Which is a really bad design. You should stick with the inheritance model, which is much more scalable than this one you want to do at any cost. – Jorge Campos Jul 25 '18 at 14:12
  • And it is simple not possible to add a foreign key for two different tables in one table for the same column. It is not allowed, nor in SQL Ansi nor in any database in the market that support FK constraints. – Jorge Campos Jul 25 '18 at 14:22
  • Just to show how bad it is to have a FK for each table in repair. Today you have two types, imagine if you add to your model 30 more types of vehicles, you would have to add 30 more FKs to repair, can you imagine the headache for it? – Jorge Campos Jul 25 '18 at 14:24
  • Why would I have to add 30 more FKs? A simple composite key of two keys should define all possibilities, doesn't it? It's only a new type ID what is evtl. new. – user2511599 Jul 25 '18 at 14:26
  • No, because you can't answer this: Composite with which key from which table? eg `repair (id, fk_tank? | fk_sport? )` that's simple not possible in sql. – Jorge Campos Jul 25 '18 at 14:28
  • type_id = 1 = sportscar, type_id = 2 = tank so I know which table. – user2511599 Jul 25 '18 at 14:31
  • ok, you have the repair and the type for which vehicle? – Jorge Campos Jul 25 '18 at 14:32
  • Here is what I'm saying: repair ( id=1, typ=1 ) you have a repair for a sportscar, but you don't know for which sportscar, to know it you should have repair ( id=1, typ=1, sportid=1 ) that's ok but now, you can't have at this table a tank to be able to have **in your model**: repair ( id=1, typ=1, sportid=1, tankid=null ) to a sport car or repair ( id=1, typ=2, sportid=null, tankid=1 ) to a tank. Understand now? – Jorge Campos Jul 25 '18 at 14:52
  • no you don't need tankid. You have repair (id, vehtype_id=2, veh_id=1) => so I know it's from table tank, and the id is 1. It could be vehtype_id=1, veh_id=1 => so I know it's id 1 from sportscar. If I have a new type, I can just add it to table vehtype, and create a new table, e.g. airplane (type_id=3), Now in repair it can be (id, vehtype_id=3, veh_id=1), so id 1 from airplane. I thought this way you don't need a common table. But it doesn't really matter, because as you say: _it is simple not possible to add a foreign key for two different tables in one table for the same column_ – user2511599 Jul 26 '18 at 05:35
  • However, I was thinking that a composite key should allow to do it. Why? Because of vehtype_id => it's always clear to which table you refer. – user2511599 Jul 26 '18 at 05:40
  • I would not allow to downvote any questions, because it's just silly. What can we achieve with that? If someone is searching for something, he/she wants to be a better person with more knowledge. Now with downvote we are saying: you are stupid, go home and remain like this for ever. If there is no possibility to downvote, the worst that can happen is you don't get any answer, and it would be much more human. You can think maybe your approach it's not completely alright, and you can think about it, how can I ask better, or how can I first understand better what my problem is. – user2511599 Jul 26 '18 at 05:47
  • I think this thread describes exactly the same: https://dba.stackexchange.com/questions/30115/foreign-key-of-multiple-tables upvotes 4x, so is it a silly question or not? Sometimes you can't perfectly define your problem, and you don't find the right answer but now I know more and I can better search and voila I'm not the only one who want to know the same thing. Anyway, I would suggest to mysql (oracle) to allow reference to multiple tables in case of composite FK. :) – user2511599 Jul 26 '18 at 06:02
  • I didn't downvote your question. You have no way to know who was, so don't offend people without knowing for sure (like, downvoter leaves a comment, then you know). I was just Off. So, reading your comments, you get consufed with your own models it seems. Currently vehicle is just a TYPE, therefore you can only have one combination of repair per type you still doesn't know which one (sport or tank) received the repair. And the link you provided describes exactly what I'm trying to teach you since the beginning. Multiple inheritance, and exaclty with the specialized keys as primary in other.. – Jorge Campos Jul 26 '18 at 23:17
  • table... look closely to the script. "Anyway, I would suggest to mysql (oracle) to allow reference to multiple tables in case of composite FK" this is just impossible for a series of reasons. – Jorge Campos Jul 26 '18 at 23:18
  • Sorry, I didn't mean that you have downvoted it, I have addressed my comment to the one who has (I know we can't know who he/she was). – user2511599 Jul 27 '18 at 05:42
  • Of course I'm confused with how should I do it, if I would know I wouldn't be here. You can only get better if you try to break your boundaries and step into a land you don't know (yet). Anyway, thanks a lot for your comments, I understand now what's the point. As there are many similar questions already I would simply delete it if that's OK for you. – user2511599 Jul 27 '18 at 06:53
  • No need to delete. This discussion is also valuable to whoever sees it, since there are good images on it. Let it be. You could accept the answer from @Zack since he said the same as me and also provided a good link to discussion. Cheers, – Jorge Campos Jul 27 '18 at 12:15

1 Answers1

0

As @Jorge has said, your Repairs table needs to link to a base Cars (or Vehicles) table, that stores both sports cars and tanks. Something like Vehicles(vehicle_id, vehicle_type, other_atts).

Depending on what information you're storing about tanks and sports cars, you can create specialized tables as necessary. Representing inheritance in SQL is a tricky topic, but can be done in a number of ways. For example, see How can you represent inheritance in a database?.

Zack
  • 2,220
  • 1
  • 8
  • 12
  • so I need a main common base table anyway, right? Class Table Inheritance (aka Table Per Type Inheritance) - that's what I need. Looks good. I think I get the picture now. That's why @jorgecampos said there will be a 1:1 relationship? – user2511599 Jul 25 '18 at 14:23
  • Yes, exactly it. The PK in both specialized tables is also the FK for the parent table, that will make it 1:1 relationship. – Jorge Campos Jul 25 '18 at 14:25
  • Yes, but it depends on what information you're storing about tanks and sports cars. You could also have a table, `VehicleAttributes`, that can list multiple attributes for a single vehicle. – Zack Jul 25 '18 at 14:26
  • Thank you for your comment. As there are many similar questions already I would simply delete it if that's OK for you. – user2511599 Jul 27 '18 at 06:54