3

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.

1

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.

  • Thank you so much for your time. Yes, I am still very new to database design and really want to master this field. By the way, just for clarification, If merge other details like body and location, in message table, i'm just going to set them to nullable right? so when I save a type text, lat and lng will be set to null and vice versa – Haunter Feb 27 '17 at 7:05
  • Yes, correct, you would want to set them to NULLable in the table definition if they are not required fields. I'll edit my answer to include that. – Ivan McA Feb 27 '17 at 7:56
0

Too many tables

message
    -id
    -body
    -latitude
    -longitude

message_image
    -message_id
    -image_path
    -image_height
    -image_width

message_video
    -message_id
    -video_path
  • Why? You can decide to have (latitude, longitude) null, if not known, or you can decide to have them in a separate table. Both approaches are legitimate. This is probably not very common, but there's nothing conceptually wrong. You may want to separate sender_id from message body because of access privileges (anonymazing, for instance) or different usages... – joanolo Feb 27 '17 at 7:41
  • @joanolo You may want to do a lot of things. Do what you like. – paparazzo Feb 27 '17 at 7:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.