CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
MackDaddy
MackDaddy

Reputation: 601

Database design - unique data from multiple sources

I have a table that stores information about pictures for automobiles. In a nutshell it contains the fields 'id', 'auto_id', 'name', and 'path'. So it is linked to a particular automobile entry through the 'auto_id' field.

Now consider that I want to add pictures for houses. Would it be better to just create another table similar to this one, or add a field in the existing table to point out the type of picture it is? Or is there an altogether better way to address this type of issue?

Edit: I apologize for the wording. It's obviously a simple problem, I just don't know how to best form it into a coherent question. Thanks for the patience and any help.

Upvotes: 0

Views: 620

Answers (3)

Francisco Alvarado
Francisco Alvarado

Reputation: 2813

Well, now given also that:

The 'id' field was just a unique identifier for the picture. The 'auto_id' field was the foreign key for the automobile the picture was linked to.

I would like to have it set up to where one can have as many photos linked to cars or houses as one would like.

My suggested solution is:
data model

Where

  • picture is your original pictures table, only that auto_id now is subject_id.
  • subject is your original automobiles table, now it will store both cars and houses records. Add a new field type_id as foreign key to subject types table.
  • subject_type is a new table where you'll store all possible subjects (not limited to cars and houses only, thinking to future expansion of your subject types).

(Sorry if this diagram doesn't reflect your real number/name/datatype of tables/columns, it's just that I don't know that info)

Upvotes: 1

Francisco Alvarado
Francisco Alvarado

Reputation: 2813

Given that only the subject (car or house) is what varies, and maybe in a future you will want to increase the number of subjects, I suggest you to keep all in one single table, just adding an extra field to store photo subject.

BTW: what is the difference between id and auto_id fields? If there is none, I'd suggest to get rid of auto_id column

Upvotes: 0

Mike Lewis
Mike Lewis

Reputation: 64137

I would just model your picture table as:

id name path

and have a join table subjects table:

picture_id subject_id subject_type

Where picture_id is a FK to pictures, and subject_id is a FK to the specific subject deemed by subject_type(automobile, house etc)

Upvotes: 1

Related Questions