Database design for an entity that might comprise from other same entities

Question

I have some sport facilities that have fields that one can play 5x5 football in them. I am trying to make a simple reservation system for them. My problem is that some fields combine and make bigger fields that the manages of the facilities want to treat them as their own entities (makes sense, if they book them like that why not).

Let's give an actual example. We have facility FA. They have 3 5x5 fields one next to another, let's call then sa, sb, sc and any two of them can combine to make a 7x7 field, let's call it dd and all three to make a 10x10 field, let's call it te. This happens with the other facilities as well but this is the more extreme case.

I have been trying to think how to model the tables for the fields when I make the reservation and deal with it but I am not sure.

One solution I have is to have a table for the fields

CREATE TABLE IF NOT EXISTS field (
    id              SMALLINT(5) UNSIGNED    NOT NULL AUTO_INCREMENT,
    arena_id        SMALLINT(4) UNSIGNED    NOT NULL,
    internal_id     TINYINT(3) UNSIGNED     NOT NULL,
    is_composite    BOOLEAN                 NOT NULL DEFAULT FALSE,

    PRIMARY KEY (id),
    UNIQUE (arena_id, internal_id),

    CONSTRAINT fk_field_arena_id FOREIGN KEY (arena_id) REFERENCES arena(id) ON UPDATE CASCADE ON DELETE CASCADE
)
;

And theh have a one to one or zero relationship with another table

CREATE TABLE IF NOT EXISTS field_component (
    field_id        SMALLINT(5) UNSIGNED    NOT NULL,
    component       SMALLINT(5) UNSIGNED    NOT NULL,

    PRIMARY KEY (field_id, component),

    CONSTRAINT fk_field_component_field_id FOREIGN KEY (field_id) REFERENCES field(id) ON UPDATE CASCADE ON DELETE CASCADE,
        CONSTRAINT fk_field_component_field_id2 FOREIGN KEY (component) REFERENCES field(id) ON UPDATE CASCADE ON DELETE CASCADE

)
;

that will have entries with the fields that comprise a component field. An entry will exist here only when the flag is_composite in the field table is true.

A simpler solution I was thinking that is a bit more manual, was instead of having the second table and the flag, to just have a string column where I put the ids of the fields that make the composite field as a comma separated list.

On a separate note, I was thinking of moving the flag is composite to a third table called field_info that I might have one to one relationship with the field and will contain information about each field. i.e. size, material of the ground, if it's composite or not, notes about it etc.

Any thought suggestions, criticism, alternatives are welcome.

Answer 1

I would consider the following, that ensures in the composite table field_component that the child and composite arenas are at least the same. Note that InnoDB check constraints are not enforced.

Point #1: The is_composite quality in the following for the field_component implicitly pointing back to something that is truly a composite is not enforced. It could be with more compositing (meaning more tables).

Point #2: The datatypes should not be overly engineered into small and tiny INTs at this stage or perhaps ever. Especially if new to mysql.

Point #3: The FK relationships have the tendency to create KEYS for you automatically when not present in the child table. The unique key that we explicitly have in field_component effectly serves two purposes. It enforces non-dupes, and it serves as the index used where an FK auto-gen one would have been generated. Another one is generated automatically as can be seen in show create table. So, our UNIQUE KEY serves a few purposes there.

CREATE TABLE IF NOT EXISTS field (
    id              SMALLINT(5) UNSIGNED    NOT NULL AUTO_INCREMENT,
    arena_id        SMALLINT(4) UNSIGNED    NOT NULL,
    internal_id     TINYINT(3) UNSIGNED     NOT NULL,
    is_composite    BOOLEAN                 NOT NULL DEFAULT FALSE,

    PRIMARY KEY (id),
    UNIQUE (arena_id, internal_id),

    CONSTRAINT fk_field_arena_id FOREIGN KEY (arena_id) REFERENCES arena(id) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS field_component (
    field_id        SMALLINT(5) UNSIGNED    NOT NULL,
    component       SMALLINT(5) UNSIGNED    NOT NULL,

    PRIMARY KEY (field_id, component),

    CONSTRAINT fk_field_component_field_id FOREIGN KEY (field_id) REFERENCES field(id) ON UPDATE CASCADE ON DELETE CASCADE,
        CONSTRAINT fk_field_component_field_id2 FOREIGN KEY (component) REFERENCES field(id) ON UPDATE CASCADE ON DELETE CASCADE

);

TweakA:

CREATE SCHEMA TweakA;
USE TweakA;

-- drop table arena
CREATE TABLE IF NOT EXISTS arena
(   id INT PRIMARY KEY,
    aName varchar(200) NOT NULL
);

-- drop table field
CREATE TABLE IF NOT EXISTS field
(   id INT AUTO_INCREMENT PRIMARY KEY,
    arena_id INT NOT NULL, -- like the Arena #
    internal_id INT NOT NULL, -- 1, 2, 3 for the field #
    is_composite BOOLEAN NOT NULL DEFAULT FALSE,
    friendly_name VARCHAR(100) NOT NULL,
    UNIQUE KEY (arena_id, internal_id),
    CONSTRAINT fk_field_arena_id FOREIGN KEY (arena_id) REFERENCES arena(id) ON UPDATE CASCADE ON DELETE CASCADE
);

-- drop table field_component
CREATE TABLE IF NOT EXISTS field_component
(   id INT AUTO_INCREMENT PRIMARY KEY,
    arena_id INT NOT NULL,
    child_internal_id INT NOT NULL,
    composite_internal_id INT NOT NULL,

    -- The following UK will pick up part of what I will explain in the Narrative
    UNIQUE KEY `unq_arena_comp_child` (arena_id,child_internal_id,composite_internal_id),

    CONSTRAINT fk_field_child_field_id FOREIGN KEY (arena_id,child_internal_id) 
        REFERENCES field(arena_id, internal_id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_field_composite_field_id FOREIGN KEY (arena_id,composite_internal_id) 
        REFERENCES field(arena_id, internal_id) ON UPDATE CASCADE ON DELETE CASCADE

    -- note that InnoDB check constraints are not effective
);

-- Note, look at output from the following
-- show create table field_component; -- this shows the auto-gen of 1 key due to FK
-- 

-- The following block is a Helper block during testing
-- Truncate in reverse order:
-- TRUNCATE TABLE field_component;
-- TRUNCATE TABLE field;
-- TRUNCATE TABLE arena;

-- test data load:
INSERT arena(id,aName) VALUES (1,'Boston Arena, North Shore');

INSERT field(arena_id,internal_id,is_composite,friendly_name) VALUES
(1,1,FALSE,'sa'),
(1,2,FALSE,'sb'),
(1,3,FALSE,'sc'),
(1,4,TRUE,'dab'),
(1,5,TRUE,'dac'),
(1,6,TRUE,'dbc'),
(1,7,TRUE,'abc');

INSERT field_component(arena_id,child_internal_id,composite_internal_id) VALUES
(1,1,4),
(1,2,4),

(1,1,5),
(1,3,5),

(1,2,6),
(1,3,6),

(1,1,7),
(1,2,7),
(1,3,7); -- SUCCESS

INSERT field_component(arena_id,child_internal_id,composite_internal_id) VALUES
(2,2,4); -- will fail, as expected

INSERT field_component(arena_id,child_internal_id,composite_internal_id) VALUES
(1,72,4); -- will fail, as expected

INSERT field_component(arena_id,child_internal_id,composite_internal_id) VALUES
(1,1,444); -- will fail, as expected

show create table field_component;
-- the above will exhibit the AUTO_INCREMENT gap anomoly due to the above
-- expected failed inserts, setting AI=13 or so

DROP SCHEMA TweakA;

I wrote an FK Enforces Composite Relationship answer that got a little complicated. Yours can go that route depending on the level of DB Enforcement you are looking for.

Also see the MySQL Using FOREIGN KEY Constraints concerning auto-gen of KEYS due to FK relationships as mentioned in Point #3.

So, this answer could just keep growing as you work thru enforcement. Or do it client side. If it were me, I would do it DB Enforcement.

Regardless, as mentioned in comments, don't store CSV values in a column.