Database Design – Best Way to Handle Different Attachment Types

database-designnormalizationtable

I am currently Building a table for message where user can send a message with different types of attachment,

Sender can:

send text per message
send one location per message
send one or multiple images per message
send one or multiple videos per message

Currently, this is my table design,

message 
    -id
    -type (text,images,videos,location)
    -sender_id

message_text
    -id
    -message_id
    -body

message_location
    -id
    -message_id
    -latitude
    -longitude

message_images
    -id
    -message_id
    -image_path
    -image_height
    -image_width

message_videos
    -id
    -message_id
    -video_path

Am I doing things right?

My concern was user can attach multiple of images or videos but only one location.

Best Answer

You are probably over-complicating matters by using separate tables for message, message_text and location. Just merge them into one table:

message
-id
-type
-sender_id
-body
-latitude
-longitude

If the columns may not have values, be sure to set them to NULLable in the table definition.

There are various valid reasons for using one to one relationships, for example for example if you expect to have a lot of records with shorter fields in the primary table but then only occasionally need records with longer fields in the secondary table but in most cases they are not necessary and only serve to complicate your design which will require more joins it otherwise would not.

From the general tone of your question you seem to be new to database design and I doubt you really want to do this. Keep everything about the message in one table and then you can inherently only have one location, one body, etc.

IF you wanted to create a one-to-one relationship the usual way you would go about this would be to make the primary key on the secondary table also a foreign key referencing the primary table. So both tables would have the same primary key value for a given record- this would ensure you could only have one.

https://www.mkyong.com/mysql/how-to-define-one-to-one-relationship-in-mysql/

As it is now, you could have multiple records in these tables, a one-to-many relationship.

But I would strongly suggest you just merge into one table.

Your attachment and image tables look fine, that will be a one-to-many relationship with separate primary keys and linked using a foreign key.

Related Solutions

Need help designing table with list of IDs to store

Do not use a list of ids. You will regret it. Use junction tables.

DDL:

create table organization (
  organization_id bigserial primary key,
  name text not null
);

create table individual (
  individual_id bigserial primary key,
  email_address varchar(255) not null
);

create table organization_member (
  organization_id bigint references organization(organization_id),
  individual_id bigint references individual(individual_id),

  primary key (organization_id, individual_id)
);

create table email_message (
  email_message_id bigserial primary key,
  send_at datetime not null,
  subject varchar(80),
  body text
);

create table email_message_organization (
  email_message_id bigint references email_message(email_message_id),
  organization_id bigint references organization(organization_id),

  primary key (email_message_id, organization_id)
);

DML:

-- add an org:

insert into organization (name) values ('Acme, Inc');

-- add an individual:

insert into individual (email_address) values ('user@example.com');

-- associate individual w org:

insert into organization_member values (1,1);

-- add an email message:

insert into email_message (send_at, subject) values ('2013-07-31 12:12:12', 'test');

-- associate an email message with an org:

insert into email_message_organization values (1, 1);

-- when it's time to send the email, get the right people:

select 
e.subject, 
e.body, 
i.email_address 
from individual i 
join organization_member om using (individual_id) 
join email_message_organization emo using (organization_id) 
join email_message e using (email_message_id);

-- send the emails, record that they were sent in another table

You should use the Party model for representing individuals and organizations, which is not what I used, but you should still look it up.

Sql-server – One or several tables for attributes

A couple of reasons I prefer a single table:

(1) with multiple tables you'll almost always be performing unions and you pay for the core data multiple times

(2) you can easily optimize for a subset of attributes or a combination of devices and attributes with indexes and use sparse columns to minimize wasted space

I wrote a bit about EAV here - it is by no means perfect, but we used it to solve many performance problems (at the cost of others) at a previous job.

Best Answer

Related Solutions

Need help designing table with list of IDs to store

Sql-server – One or several tables for attributes

Related Question