VPF::Пустующие колонки в таблице - Форум программистов

afon

Дата 15.1.2010, 20:50 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 85
Регистрация: 5.4.2008
Где: Украина, Киев

Репутация: нет
Всего: 1

Всем привет.
Делаю такую простенькую таблицу некоей статистики при удалении ордеров по заказанным, но не полученным товарам.
- Товар могли заказать, но не оплатить, передумали покупать
- ТОвар могли заказать, оплатить, но не прийти за ним, бывает всякое.
Таких неполученных товаров (то есть заявок по ним, ордеров) очень много, поэтому заявки на них я тру раз в сутки и делаю по ним стистику.

Структурка:

id (int)
магазинId (int)
группаМагазиновId (int)
количествоНереализованныхЗаявок (int)
продуктБылОплачен (bool)
дата (timestamp)

Ситуация: в разных строках могут пустовать колнки магазинId или группаМагазиновId. Ну, то есть товары могут быть сгурппированные по магазинам или по группам магазинов.

Вопрос: на сколько такая ситуация с пустующими колонками плоха для базы данных и будет ли хорошим тоном (и вообще правильно) сделать две таблицы: одну для профуканных товаров по магазинам и одну для профуканным товарам для группМагазинов?

PS: база apache Derby

afon

Дата 15.1.2010, 21:05 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 85
Регистрация: 5.4.2008
Где: Украина, Киев

Репутация: нет
Всего: 1

Понял, что вопрос глупый smile

Тему можно удалить или пофлеймить при желании.

Gluttton

Дата 16.1.2010, 16:33 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 10
Всего: 54

Цитата(afon @ 15.1.2010, 21:05

)

Тему можно удалить или пофлеймить при желании.

А вот это всегда пожалуйста smile

...

Мне кажеться Вы пытаетесь решить задачу с конца smile

...
Изначально определитесть с предметной областью, которую должна описывать БД.
Например:

Цитата

БД должна хранить информацию о заказах, а так же о магазинах, в которых эти заказы были сделаны...
Магазины образуют группы магазинов. В группе магазинов может состоять один и более магазинов.
В информацию о заказе обязательно должна быть включена информация о дате оплаты (при отсутствии даты заказ считается не оплаченным), а так же признак того получен ли заказ или нет.

ОК. Итак выделим существительные, глаголы и прилагательные...

Цитата

Существительные:
Заказ, магазин, группа магазинов

Глаголы:
Входить (в контексте "... в группу магазинов входят слудующие магазины"), заказывать ("...зыказы были сделаны")

Прилагательные:
Оплаченность (признак оплаты), полученность (признак получения).

Очень натянуто получилось, но я думаю, в целом картина ясна smile

...

ОК. Движемся дальше.
Существительные будут сущностями, глаголы связями между ними, а прилагательные атрибутами...
Т.е. получим что-то вроде такого:

Цитата

                   Магазины              Заказ
               Входят в группу       производиться
                   магазинов          в магазине

Группа магазинов <----------< Магазин <----------< Заказ
                     1:М*                  1:М     |
                                                   |-- Оплата
                                                   |
                                                   \-- Получение

* - При этом будем считать, что один магазин не может состоять одновременно в нескольких группах.

Т.о. у нас должно получиться следующие таблицы:
Группы
- код группы;
- имя группы;
- описание.

Магазины:
- код магазина;
- код группы;
- имя магазина;
- описание.

Заказы:
- код заказа;
- код магазина;
- оплата;
- получение;
- дата оформления заказа.

ОК. Движемся дальше... Препарирование...
СУБД: Firebird 2.5.

Код


/*============================================================*/
/*=== Create DataBase TR.FDB                               ===*/
/*============================================================*/

SET AUTODDL ON;

CONNECT DATABASE 'Market'
USER 'SYSDBA' PASSWORD 'masterkey';
DROP DATABASE;

SET SQL DIALECT 3;
SET NAMES WIN1251;
CREATE DATABASE 'Market'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET WIN1251;

/*============================================================*/
/*=== Create Domains                                       ===*/
/*============================================================*/

CREATE DOMAIN ID        AS BIGINT                      NOT NULL;
CREATE DOMAIN NAME      AS VARCHAR(20)                 NOT NULL;
CREATE DOMAIN RECORD    AS VARCHAR(50);
CREATE DOMAIN GET       AS VARCHAR(10) DEFAULT 'No'    NOT NULL;

/*============================================================*/
/*=== Create Generators                                    ===*/
/*============================================================*/

CREATE GENERATOR GROUPS;
CREATE GENERATOR MARKETS;
CREATE GENERATOR ORDERS;

/*============================================================*/
/*=== Create Tables                                        ===*/
/*============================================================*/

CREATE TABLE GROUPS
(
    ID           ID,
    NAME         NAME,
    DESCRIPT     RECORD
);

CREATE TABLE MARKETS
(
    ID           ID,
    GROUPS       ID,
    NAME         NAME,
    DESCRIPT     RECORD
);

CREATE TABLE ORDERS
(
    ID           ID,
    MARKETS      ID,
    PAY          DATE,
    GET          GET,
    OPERATION    DATE,
    DESCRIPT     RECORD
);

/*============================================================*/
/*=== Declaration primary keys                             ===*/
/*============================================================*/

ALTER TABLE GROUPS   ADD CONSTRAINT PK_GROUPS   PRIMARY KEY(ID);
ALTER TABLE MARKETS  ADD CONSTRAINT PK_MARKETS  PRIMARY KEY(ID);
ALTER TABLE ORDERS   ADD CONSTRAINT PK_ORDERS   PRIMARY KEY(ID);

/*============================================================*/
/*=== Declaration foreigns keys                            ===*/
/*============================================================*/

ALTER TABLE MARKETS
    ADD CONSTRAINT FK_GROUPS_MARKETS
    FOREIGN KEY(GROUPS)
    REFERENCES GROUPS(ID);

ALTER TABLE ORDERS
    ADD CONSTRAINT FK_MARKETS_ORDERS
    FOREIGN KEY(MARKETS)
    REFERENCES MARKETS(ID);

/*============================================================*/
/*=== Create Triggers                                      ===*/
/*============================================================*/

SET TERM ^;

CREATE TRIGGER GROUPS_ID FOR GROUPS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL)
    THEN NEW.ID=GEN_ID (GROUPS, 1);
END^

CREATE TRIGGER MARKETS_ID FOR MARKETS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL)
    THEN NEW.ID=GEN_ID (MARKETS, 1);
END^

CREATE TRIGGER ORDERS_ID FOR ORDERS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL)
    THEN NEW.ID=GEN_ID (ORDERS, 1);
END^

/*============================================================*/
/*=== Create Procedures                                    ===*/
/*============================================================*/

/*------------------------------------------------------------*/
/*--- Create procedure SELECT_NOT_PAY                      ---*/
/*------------------------------------------------------------*/

RECREATE PROCEDURE SELECT_NOT_PAY
(
    MIN_DATE DATE,
    MAX_DATE DATE
)
RETURNS
(
    GROUPS    NAME,
    MARKETS   NAME,
    ORDERS    ID,
    DESCRIPT  RECORD,
    OPERATION DATE
)
AS
BEGIN
    FOR SELECT DISTINCT
        GROUPS.NAME,
        MARKETS.NAME,
        ORDERS.ID,
        ORDERS.DESCRIPT,
        ORDERS.OPERATION
    FROM GROUPS
        INNER JOIN MARKETS
        ON GROUPS.ID=MARKETS.GROUPS
            INNER JOIN ORDERS
            ON MARKETS.ID=ORDERS.MARKETS
        WHERE ORDERS.PAY IS NULL
    INTO :GROUPS, :MARKETS, :ORDERS, :DESCRIPT, :OPERATION
    DO
    SUSPEND;
END ^

/*------------------------------------------------------------*/
/*--- Create procedure COUNT_MARKETS_NOT_PAY               ---*/
/*------------------------------------------------------------*/

RECREATE PROCEDURE COUTN_MARKETS_NOT_PAY
(
    MIN_DATE DATE,
    MAX_DATE DATE
)
RETURNS
(
    GROUPS    NAME,
    MARKETS   NAME,
    CNT       INT
)
AS
BEGIN
    FOR SELECT DISTINCT
        GROUPS.NAME,
        MARKETS.NAME,
        COUNT(ORDERS.ID)
    FROM GROUPS
        INNER JOIN MARKETS
        ON GROUPS.ID=MARKETS.GROUPS
            INNER JOIN ORDERS
            ON MARKETS.ID=ORDERS.MARKETS
        WHERE ORDERS.PAY IS NULL
        GROUP BY GROUPS.NAME, MARKETS.NAME
    INTO :GROUPS, :MARKETS, :CNT
    DO
    SUSPEND;
END ^

/*------------------------------------------------------------*/
/*--- Create procedure COUNT_GROUPS_NOT_PAY                ---*/
/*------------------------------------------------------------*/

RECREATE PROCEDURE COUNT_GROUPS_NOT_PAY
(
    MIN_DATE DATE,
    MAX_DATE DATE
)
RETURNS
(
    GROUPS    NAME,
    CNT       INT
)
AS
BEGIN
    FOR SELECT DISTINCT
        GROUPS.NAME,
        COUNT(ORDERS.ID)
    FROM GROUPS
        INNER JOIN MARKETS
        ON GROUPS.ID=MARKETS.GROUPS
            INNER JOIN ORDERS
            ON MARKETS.ID=ORDERS.MARKETS
        WHERE ORDERS.PAY IS NULL
        GROUP BY GROUPS.NAME
    INTO :GROUPS, :CNT
    DO
    SUSPEND;
END ^

/*------------------------------------------------------------*/
/*--- Create procedure SELECT_NOT_GET                      ---*/
/*------------------------------------------------------------*/

RECREATE PROCEDURE SELECT_NOT_GET
(
    MIN_DATE DATE,
    MAX_DATE DATE
)
RETURNS
(
    GROUPS    NAME,
    MARKETS   NAME,
    ORDERS    ID,
    DESCRIPT  RECORD,
    OPERATION DATE
)
AS
BEGIN
    FOR SELECT DISTINCT
        GROUPS.NAME,
        MARKETS.NAME,
        ORDERS.ID,
        ORDERS.DESCRIPT,
        ORDERS.OPERATION
    FROM GROUPS
        INNER JOIN MARKETS
        ON GROUPS.ID=MARKETS.GROUPS
            INNER JOIN ORDERS
            ON MARKETS.ID=ORDERS.MARKETS
        WHERE ORDERS.GET='No'
        AND ORDERS.OPERATION>=:MIN_DATE
        AND ORDERS.OPERATION<=:MAX_DATE
    INTO :GROUPS, :MARKETS, :ORDERS, :DESCRIPT, :OPERATION
    DO
    SUSPEND;
END ^

/*------------------------------------------------------------*/
/*--- Create procedure COUNT_MARKETS_NOT_GET               ---*/
/*------------------------------------------------------------*/

RECREATE PROCEDURE COUNT_MARKETS_NOT_GET
(
    MIN_DATE DATE,
    MAX_DATE DATE
)
RETURNS
(
    GROUPS    NAME,
    MARKETS   NAME,
    CNT       INT
)
AS
BEGIN
    FOR SELECT DISTINCT
        GROUPS.NAME,
        MARKETS.NAME,
        COUNT(ORDERS.ID)
    FROM GROUPS
        INNER JOIN MARKETS
        ON GROUPS.ID=MARKETS.GROUPS
            INNER JOIN ORDERS
            ON MARKETS.ID=ORDERS.MARKETS
        WHERE ORDERS.GET='No'
        GROUP BY GROUPS.NAME, MARKETS.NAME
    INTO :GROUPS, :MARKETS, :CNT
    DO
    SUSPEND;
END ^

/*------------------------------------------------------------*/
/*--- Create procedure COUNT_GROUPS_NOT_GET                ---*/
/*------------------------------------------------------------*/

RECREATE PROCEDURE COUNT_GROUPS_NOT_GET
(
    MIN_DATE DATE,
    MAX_DATE DATE
)
RETURNS
(
    GROUPS    NAME,
    CNT       INT
)
AS
BEGIN
    FOR SELECT DISTINCT
        GROUPS.NAME,
        COUNT(ORDERS.ID)
    FROM GROUPS
        INNER JOIN MARKETS
        ON GROUPS.ID=MARKETS.GROUPS
            INNER JOIN ORDERS
            ON MARKETS.ID=ORDERS.MARKETS
        WHERE ORDERS.GET='No'
        GROUP BY GROUPS.NAME
    INTO :GROUPS, :CNT
    DO
    SUSPEND;
END ^

SET TERM ;^

COMMIT;

И в результате получаем за указанный период:
- количество неоплаченных заказов с группировкой по группам магазинов;
- количество неоплаченных заказов с группировкой по магазинам;
- описание и перечень неоплаченных заказов;
- количество неполученных заказов с группировкой по группам магазинов;
- количество неполученных заказов с группировкой по магазинам;
- описание и перечень неполученных заказов;
Вызовом соответствующей процедуры с указанием временного диапазона.

Например, выполнив следующий запрос:

Код


/*============================================================*/
/*=== Insert data in tables                                ===*/
/*============================================================*/

INSERT INTO GROUPS(NAME) VALUES ('Gipermarkets');
INSERT INTO GROUPS(NAME) VALUES ('Supermarkets');
INSERT INTO GROUPS(NAME) VALUES ('Minimarkets');

COMMIT;

INSERT INTO MARKETS(GROUPS, NAME)
    SELECT ID, 'Ashan'
    FROM GROUPS
        WHERE NAME='Gipermarkets';
INSERT INTO MARKETS(GROUPS, NAME)
    SELECT ID, 'Otto'
    FROM GROUPS
        WHERE NAME='Gipermarkets';
INSERT INTO MARKETS(GROUPS, NAME)
    SELECT ID, 'MyWay'
    FROM GROUPS
        WHERE NAME='Supermarkets';
INSERT INTO MARKETS(GROUPS, NAME)
    SELECT ID, 'Frash'
    FROM GROUPS
        WHERE NAME='Minimarkets';

COMMIT;

INSERT INTO ORDERS(MARKETS, PAY, GET, OPERATION)
    SELECT ID, CURRENT_DATE, 'Yes', CURRENT_DATE
    FROM MARKETS
        WHERE NAME='Ashan';
INSERT INTO ORDERS(MARKETS, PAY, OPERATION)
    SELECT ID, CURRENT_DATE, CURRENT_DATE
    FROM MARKETS
        WHERE NAME='Ashan';
INSERT INTO ORDERS(MARKETS, OPERATION)
    SELECT ID, CURRENT_DATE
    FROM MARKETS
        WHERE NAME='Ashan';
INSERT INTO ORDERS(MARKETS, OPERATION)
    SELECT ID, CURRENT_DATE-1
    FROM MARKETS
        WHERE NAME='Frash';

COMMIT;

И заполнив таблицы следующими данными:
user posted image

И выполнив вот такой не хитрый запрос:

Код


select * from select_not_pay(current_date, current_date)

Мы получим вот такой замечательный результат:
user posted image

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

Слава Україні!

afon

Дата 16.1.2010, 21:30 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 85
Регистрация: 5.4.2008
Где: Украина, Киев

Репутация: нет
Всего: 1

Gluttton. Потрясающе smile

Я про результат. Ибо, к своему стыду, в жизни не пользовался процедурами.
И надеюсь, что на это флейм вы не потратили весь день smile

ато бы у меня ушел весь день.

Теперь к делу.
1) я задумался об использовании встроенных процедур. Но тогда возникает вопрос: что для базы выполнить быстрее и легче с точки зрения производительности - процедуру или запрос от приложения? Моя профанская точка зрения говорит , что базе будет все равно.

2) Касаясь моих собственных баранов: у меня между магазином и заказом есть еще товар. Товар может быть или в магазине, или в группе, но не одновременно. А вот магазин может как иметь свои товары, так и быть в группе магазинов, у которых свои товары, а может и не быть в группе. Поэтому ситуации, при которой мы получим вот такой замечательный результат, просто не будет smile

Либо колонка GROUPS, либо колонка MARKETS будет пустовать, потому что конечная сущность ЗАКАЗ примаплен связью на продукт, продукт примаплен и на магазин, и на группу, при чем оба поля nullable, ну, иначе бы я не реализовал однозначность привязки. И еще, в данном конкретном случае использовать выборку в любой нужный мне момент я не могу, "профуканные" заказы нужно тереть, иначе я, пардон, закакаю винт за неделю. У меня один клиент будет генерить таких "профуканных" заказов до 1 го в секунду, это правда максимум. Это 36 000 мусорных записей в одной таблице от одного клиента при 10 часах работы. Так вот этот мусор нужно тереть, а статистику по нему хранить. И в этой статистике будут путые ячейки smile

Я надеюсь теперь яснее.

3) я решил что вопрос тупой, потому что, я уверен, у всех есть таблица "юзер", и в этом юзере есть незаполненные поля. Например, отчество, компания, год рождения и тп. Те же пустые ячейки, и никто не парится. По этим соображениям я решил, что вопрос следует отменить.

4) и не начинал я с конца smile

у меня есть вполне конкретная база под вполне конкретное приложение, и вот такая вполне конкретная задача - чистить мусор.

5) С существительным, глаголами и прилагательными я еще в таком разрезе не сталкивался. Поэтому спасибо smile

прикольный пример проектирования.

И серьезно, сколько времени у вас ушло на этот мегоответ?

Gluttton

Дата 16.1.2010, 22:03 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 10
Всего: 54