CODEWITHSUNDEEP
mysqlsql
Anatoliy
Anatoliy

Reputation: 321

referencing columns of 2 different tables

Following schema:

archivebox (id, mediatype_id)
mediatype (id, name)
archivebox_records (id, archiveboxId, recordId)
picture (id,name)
print (id,name)

recordId should be referenced whether to pictureId or printId. How is it possible to solve this problem? I work with mysql.

Thanks!

Upvotes: 0

Views: 72

Answers (2)

JimmyB
JimmyB

Reputation: 12610

Another clean solution is to merge picture and print into 

medium( id, name, type)

where type can be "print" or "picture" or when the datamodel grows the foreign key referencing a table containing the allowed values.

Upvotes: 1

Phil
Phil

Reputation: 164744

It's always best to be explicit in your designs. This would involve creating two tables archive_pictures and archive_prints with foreign key relationships to the respective tables.

If you really don't want to go down that route, try adding some sort of record indicator to the archive_records table, eg

ALTER TABLE `archive_records` ADD `record_type` ENUM('picture', 'print') NOT NULL;

You can then create queries based on this indicator

SELECT p.name FROM picture p
INNER JOIN archive_records ar
    ON ar.record_type = 'picture' AND p.id = ar.recordId

Upvotes: 1

Related Questions