0

I have a table named booking which has a column contact_number.

What will be the best practice to insert multiple contact numbers for single booking? I have attached an image in which 1 entry has contact numbers separated with commas and other made 2 entries for same booking. Last thing i think of is to create a separate table for contacts. What will be the best way?

There will be an array for multiple contacts, I am coding in codeigniter.

enter image description here

Faisal
  • 1,907
  • 1
  • 22
  • 29
  • create separate table will be good, it will easy if you need filters etc. But if only for display purpose then separated by comma is ok. – Devsi Odedra Feb 14 '19 at 11:02
  • You need a separate table. That second table must have, besides its own id field, a field with the booking id so you can cross-reference them with a `join` clause. This allows an arbitrary number of contact entries per booking, with the "cost" of a little bit more coding to get it working – Javier Larroulet Feb 14 '19 at 11:14

4 Answers4

2

if I was you I would make a customer table with customer information. If they [ the customer ] have multiple contact numbers you can make a "more information" column (something like this) and when they book again you kown who they are, they will already havea record. It will give you a feel to remember who they are (is somthing like market strategies [maybe?])

Martin
  • 22,212
  • 11
  • 70
  • 132
WolfCode
  • 145
  • 1
  • 8
  • This is a good approach to have a separate table for the customers but my question is still there. How to manage multiple contact numbers in customer table as well. Comma separate values are not much helpful in future. – Faisal Feb 14 '19 at 11:24
  • do save in json so you can save like {"main_contact":14323452,"contact1":32547456} – M.Hemant Feb 14 '19 at 11:26
  • @Faisal this contact number for different customer ? or one customer have multi contact number ? if is different customer contact well make a main_contact columm and an other one column like second option – WolfCode Feb 14 '19 at 11:29
  • @WolfCode this is customer's contact numbers (1 or more than 1) – Faisal Feb 14 '19 at 11:36
  • @Faisal sry replay so late (lunch time) what i mean is like the frist time multi contact in one column but one column (NOT NULL) with main customer contact and other column could be null ( here you can insert the main costumer friend contact ) – WolfCode Feb 14 '19 at 12:49
2

There are 3 ways that I can think of:

  1. Using a separate table and having a one to many relationship with the booking

    • best for scalability, indexing, architecture

  2. Using a JSON string to store them in one field

    • not easily searchable, if you plan not to use it somewhere except the booking page, it's better

  3. Using multiple fields like up to 5 (contact number 1, contact number 2..)

    • if you think to assign a limit for that, and you may sometime search for a number

Choose wisely. Think what you (will) need and what you plan to do first, then choose the method.

Taha Paksu
  • 15,371
  • 2
  • 44
  • 78
1

Create an array and encode with JSON

$contact_number = array(
'mobile' => '+1 32313213',
'fax' => '+1 32432432',
'phone' => '+1 8984234234'
);

$booking_contact = json_encode($contact_number);

Now you can insert $booking_contact into your database. If you want to use it just decode JSON pass true for associative array

//Suppose $row is containing you row result

$booking_contact = json_decode($row['booking_contact'], true);

echo $booking_contact['mobile']; // Output +1 32313213
echo $booking_contact['fax']; // Output +1 32313213
echo $booking_contact['phone']; // Output +1 8984234234
Mohsin Mujawar
  • 186
  • 7
0

There is an unspoken rule in RDBMS - never ever use commaseparated strings for the purpose of m:n relations - it is simply violating the first normal form.

For more information regarding this matter take a look here

In your case you need :

  • a Table - booking (as you already mentioned)
  • a Table - contact (i assume you already have one as you already have numbers for) a Table
  • a Table - booking_contact with fields (booking_id, contact_id)

Now if you want all contacts from a booking ID you simply write in Codeigniter style

$query = $this->db
    ->select('*')
    ->from('contact c')
    ->join('booking_contact bc', 'c.contact_id = bc.contact_id', 'left')
    ->join('booking b', 'bc.booking_id = b.booking_id')
    ->where('b.booking_id', 1)
    ->get();
Atural
  • 5,389
  • 5
  • 18
  • 35