db design for hairdressers

[iamthwee]

I'm intending on doing a database for hairdressers (as an example)

So there will be:

1. Customers
2. The hairdresser can check their calendar to see who is booked on that day.
3. Customers can search of available times slots.
4. The hairdresser can enter customers onto the system, customers will have their own logon
5. Customers get a scheduled reminder an hour before their appointment.
6. Hairdresser can send promo stuff to customers every end of month.
7. There will be MULTIPLE hairdresser having there own CLIENTS

ta

______________
-other additional information, I'm using codeigniter and the handy calendar class.
http://www.youtube.com/watch?v=qMsEAtXtE2g
-would love to get a reply from diafol,prit or veedeo as these are my favs :)

[hericles]

Have you attempted any of this or do you just expect someone else to do it for you? I don't mean to be harsh but if you're not prepared to have a go yourself you're going to improve much.
From what I've seen on this site people are all too willing to help you tweak and improve your code/design but you won't find too many people who will just do it for you.

[diafol]

Heh heh. You reap what you sow!

So what's the question?

http://www.databaseanswers.org/data_models/

[almostbob]

Look at hotel booking scripts
customers are customers, still
hairdressers are analogous to suites/rooms
salons > hotels

hotels have multiple rooms: salons have chairs
rooms take boookings /day: stylists take bookings /hour+

modifying one to the other is less difficult than it looks, db schema is so similar

[iamthwee]

-- phpMyAdmin SQL Dump
-- version 3.4.10.1deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 31, 2014 at 09:49 AM
-- Server version: 5.5.34
-- PHP Version: 5.3.10-1ubuntu3.9

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `salon`
--

-- --------------------------------------------------------

--
-- Table structure for table `customers`
--

CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `salonid` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `events`
--

CREATE TABLE IF NOT EXISTS `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `custid` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `startdate` datetime NOT NULL,
  `endate` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `salon`
--

CREATE TABLE IF NOT EXISTS `salon` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `salonid` int(11) NOT NULL,
  `userid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

I came up with this.

[iamthwee]

http://stackoverflow.com/questions/8914457/checking-for-date-range-conflicts-in-mysql

I think that would be a good query to check overlaps for appointments.

[diafol]

I'm still not sure of the question to which you want a reply.

[iamthwee]

@Diafol, just wondering if the proposed table structure would work, or if you can spot any glaring issues.

[diafol]

Hmm, OK

Tables

salon - id | name | tel | address lines | map_ref
hairdressers - id | fname | lname | gender
bookings - id | salon_id | hairdresser_id | customer_id | treatment_bits (int) | treatment_extra (text)
customers - id | fname | lname | tel | mobile | rating (for blacklisting)
hair_salon - hairdresser_id | salon_id (compound PK) - if hairdresser can be in more than one salon - otherwise just tag on salon_id to hairdresser table
treatments - id | treatment | bit (1,2,4,8...) [max 32 records]

The idea behind this 'treatments' is that the operator can click on checkboxes, to build up an integer to store as opposed to text-based data, that is difficult to change back to checked/unchecked checkboxes.

Anyway, just a quick thought. You'll have researched the needs of the industry well and should be able to create a flexible schema. :)

[iamthwee]

Thanks that looks good, there will only be one hairdresser/admin for each salon, I'd also add a starttime and end time to the bookings table. That is the most important.

[diafol]

Oops - yes forgot about the datetimes :)