-1

I am creating a office letter dispatch system using PHP MySQL where a office Division is unique id dispatch item can be segmented by division_id

I can combined both Inward and Outward dispatch letter in one table by adding a column letter type

i decided to make 2 table one for Inward and one for Outward

CREATE TABLE `dispatchs_inward` (  
  `dispatch_id` bigint(20) UNSIGNED NOT NULL,
  `subject` varchar(200) CHARACTER SET latin1 DEFAULT NULL,
  `dispatch_message` varchar(10000) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `file_attached` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `dispatch_date` varchar(40) CHARACTER SET latin1 DEFAULT NULL,
  `dispatch_status` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `sender_division_id` int(10) DEFAULT NULL,
  `reciever_division_id` int(10) DEFAULT NULL,
  `reciever_status` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `sent_status` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `read_status` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `reject_status` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `reject_note` varchar(100) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `dispatchs_outward` (  
  `dispatch_id` bigint(20) UNSIGNED NOT NULL,
  `subject` varchar(200) CHARACTER SET latin1 DEFAULT NULL,
  `dispatch_message` varchar(10000) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `file_attached` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `dispatch_date` varchar(40) CHARACTER SET latin1 DEFAULT NULL,
  `dispatch_status` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `sender_division_id` int(10) DEFAULT NULL,
  `reciever_division_id` int(10) DEFAULT NULL,
  `reciever_status` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `sent_status` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `read_status` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `reject_status` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `reject_note` varchar(100) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

please help me to make it better way and what will be relation between tables ? Expert suggestion is highly appreciated.

1
  • 1
    The columns are identical, why wouldn't you just use your idea to add a LetterType?
    – user212533
    Aug 19, 2020 at 12:37

1 Answer 1

1

I would create one dispatch table with a dispatch type column. The two tables are identical, which indicates that they are the same entity. Also, inwards and outwards is vague because inwards/outwards relative to what? Having vague terms like that is indicative of a data model problem. Having two dispatch tables makes it more difficult to refer to a specific dispatch.

Is there a relationship between the two tables? The inwards vs outwards doesn't help in this case--which table is primary in the relationship. Consider the following example to show how you can have one dispatch table and another table show a relationship.

CREATE TABLE dispatch (
   `dispatch_id` bigint(20) UNSIGNED NOT NULL,
   ... snip ...
);

CREATE TABLE response_to (
  `parent_dispatch_id` bigint(20) UNSIGNED NOT NULL,
  `response_dispatch_id` bigint(20) UNSIGNED NOT NULL
);

Some other design points to consider:

  • If something is a date, use the date data type and not a varchar.
  • Recommend using numbers for items that are numeric in nature or as relational keys. Unless the status is a numeric return code, I would use a descriptive string (e.g. sent_status should be 'UNSENT', 'SENDING', 'DELIVERED', etc) or a boolean (e.g. sent_status is T/F).
  • I avoid plural words as table names. The entity being stored in one row is one dispatch, not multiple dispatches.

Storing dates as a string should be avoided because it becomes more difficult to enforce consistency in the format, arithmetic operations become more expensive, and date intervals are more expensive.

Older database designs often used numbers as a token for performance reasons (string comparisons were expensive compared to integer comparisons), but that is often not an issue for most implementations. In my experience, the use of numeric tokens makes it difficult for new users and weaken the relationship with the underlying model. For example, consider sent_status=47 vs sent_status='QUEUED', which is more understandable?

There is a fine art in database normalization and your table works. It does appear that you have merged a couple of different entities into one table. I recommend that you consider if there is a different model that might work better for your application. For example, you might want to split the content (what if there are multiple attachments) and the transmission (what if there are multiple receivers).

This is a minor annoyance on my part, but I'm not a fan of the 'dispatch_date' column name. The word 'dispatch' can have two different meanings. A dispatch can be a message (e.g. a letter, a manuscript) that was sent and it also when something was sent. My recommendation is that you avoid using entity names more than once in your design. If you want to use 'dispatch' to mean something that was sent, then you would name 'dispatch_date' as 'creation_date', 'signed_date', 'issue_date', 'sent_date', etc to describe the type of date.

2
  • Nice explain but if you can make the full example then it will more clarification
    – Firefog
    Aug 23, 2020 at 4:59
  • As much as I would like to make a full example, I really do not know enough about the data model to make anything more than a toy example. I think a mistake a lot of database designers make is they are not familiar with the facts that need to be stored and the questions the users want to ask the database--Sorry!
    – Jim D
    Aug 27, 2020 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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