MYSQL Database Structure not working properly


Ask A Question





Login To post a question

I know the mistake it’s mine but I can’t get where the mistake is cause Databases are not my strong point.

I need to make a simple Database where I store:

Those two tables, will eventually be used to merge data between and display data of a weekly Schedule where I’ll assign to each hour and day of the week (Monday-Sunday), a sport Activity instructed by only one instructor.

Then, this data will be shown with php and javascript with a appearance like this

Schedule

I had problems when I started merging data with the foreign and primary keys. This is my DB, sorry if it’s a disaster:

-- phpMyAdmin SQL Dump
-- version 4.7.7
-- https://www.phpmyadmin.net/
--
-- Servidor: localhost:8889
-- Tiempo de generación: 16-04-2018 a las 15:45:44
-- Versión del servidor: 5.6.38
-- Versión de PHP: 7.2.1

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

--
-- Base de datos: `Horario_CVCC`
--

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

--
-- Estructura de tabla para la tabla `ACTIVIDADES`
--

CREATE TABLE `ACTIVIDADES` (
  `ID` int(11) NOT NULL,
  `Nombre` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcado de datos para la tabla `ACTIVIDADES`
--

INSERT INTO `ACTIVIDADES` (`ID`, `Nombre`) VALUES
(1, 'Body Pump'),
(2, 'Fitness'),
(3, 'Group Cycling'),
(4, 'Spin & fit'),
(5, 'Abdominales'),
(6, 'Suspension Training'),
(7, 'Zumba'),
(8, 'Yoga'),
(9, 'Pilates'),
(10, 'Crosstraining'),
(11, 'Tenis'),
(12, 'Pádel'),
(13, 'Natación'),
(14, 'Aquafitness'),
(15, 'Personal Training'),
(16, 'Vision Body'),
(17, 'Club Running');

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

--
-- Estructura de tabla para la tabla `HORARIO`
--

CREATE TABLE `HORARIO` (
  `Actividad` int(11) NOT NULL DEFAULT '0',
  `Instructor` int(11) DEFAULT NULL,
  `Dia` varchar(20) DEFAULT NULL,
  `Hora_Inicio` time DEFAULT NULL,
  `Hora_Fin` time DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcado de datos para la tabla `HORARIO`
--

INSERT INTO `HORARIO` (`Actividad`, `Instructor`, `Dia`, `Hora_Inicio`, `Hora_Fin`) VALUES
(1, 6, 'Lunes', '13:30:00', '14:30:00'),
(3, 4, 'Lunes', '09:30:00', '10:30:00'),
(8, 2, 'Lunes', '08:00:00', '09:00:00'),
(14, 3, 'Lunes', '10:30:00', '11:30:00');

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

--
-- Estructura de tabla para la tabla `INSTRUCTORES`
--

CREATE TABLE `INSTRUCTORES` (
  `ID` int(11) NOT NULL,
  `Nombre` varchar(100) DEFAULT NULL,
  `URL` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcado de datos para la tabla `INSTRUCTORES`
--

INSERT INTO `INSTRUCTORES` (`ID`, `Nombre`, `URL`) VALUES
(1, 'André', ''),
(2, 'Charles', ''),
(3, 'Michael', ''),
(4, 'Cintia', ''),
(5, 'Virginia', ''),
(6, 'Marcos', ''),
(7, 'Philip', ''),
(8, 'Joanne', '');

--
-- Índices para tablas volcadas
--

--
-- Indices de la tabla `ACTIVIDADES`
--
ALTER TABLE `ACTIVIDADES`
  ADD PRIMARY KEY (`ID`);

--
-- Indices de la tabla `HORARIO`
--
ALTER TABLE `HORARIO`
  ADD PRIMARY KEY (`Actividad`),
  ADD KEY `Instructor` (`Instructor`);

--
-- Indices de la tabla `INSTRUCTORES`
--
ALTER TABLE `INSTRUCTORES`
  ADD PRIMARY KEY (`ID`);

--
-- AUTO_INCREMENT de las tablas volcadas
--

--
-- AUTO_INCREMENT de la tabla `ACTIVIDADES`
--
ALTER TABLE `ACTIVIDADES`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;

--
-- AUTO_INCREMENT de la tabla `INSTRUCTORES`
--
ALTER TABLE `INSTRUCTORES`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- Restricciones para tablas volcadas
--

--
-- Filtros para la tabla `HORARIO`
--
ALTER TABLE `HORARIO`
  ADD CONSTRAINT `horario_ibfk_1` FOREIGN KEY (`Actividad`) REFERENCES `ACTIVIDADES` (`ID`),
  ADD CONSTRAINT `horario_ibfk_2` FOREIGN KEY (`Instructor`) REFERENCES `INSTRUCTORES` (`ID`);

For more info please click herehttps://stackoverflow.com/q/49861441



Have a question or need a custom quote?
info@smartinfocare.com