[plug] Postgres categories table advice

[Chris Caston]

Hello,

I am working on the following github project:
https://github.com/affluent-bilby-classifieds/react-admin-and-postgraphile-playground

I am trying to create a menu database in postgres for a react-admin
dashboard for a burger restaurant.
I am trying to normalize the "menu" table so that it has a category_id
instead of a written char field. I have set this to  NUMERIC(2).
I then create the categories table with the id as the primary field.
Without going into further details about react-admin I will be able to
select a category from a dropdown menu so long as I have setup my postgres
database correctly.

I believe I need to JOIN the two tables (
https://www.tutorialspoint.com/postgresql/postgresql_using_joins.htm)

As I have never done this before please let me know if I am on the right
track and if so how I would apply this join. If you have any further
questions please let me know.

Thank you

Best regards,

Chris

This is my seed.sql file:

SET client_min_messages = error;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;

DROP TABLE IF EXISTS public.contacts CASCADE;

CREATE TABLE public.contacts (
    id             UUID PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL,
    email          VARCHAR(255) NOT NULL,
    firstname      VARCHAR(255),
    lastname       VARCHAR(255),
    website        VARCHAR(255),
    streetaddress  VARCHAR(255),
    phone          VARCHAR(255),
    companyname    VARCHAR(255),
    created_at     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX contacts_email_index ON public.contacts (email);


DROP TABLE IF EXISTS public.menu CASCADE;

CREATE TABLE public.menu (
    id             SERIAL PRIMARY KEY,
    title          VARCHAR(255) NOT NULL,
    category_id    NUMERIC(2)
    price          NUMERIC(8, 2),
    desc1          VARCHAR(255),
    isenabled      BOOLEAN NOT NULL DEFAULT TRUE,
    created_at     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);


DROP TABLE IF EXISTS public.menu.categories CASCADE;

CREATE TABLE public.menu.categories (
    id             SERIAL PRIMARY KEY,
    catname        VARCHAR2(255) NOT NULL,
    created_at     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);









SET client_min_messages = INFO;
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.plug.org.au/pipermail/plug/attachments/20200626/ebb9f34f/attachment.html>