Content
Project requirements
The project implements a database of IT components. Three categories are planned so far: monitors, medical printers, system units. The functionality of the database also includes the ability to create new categories.
The project should consist of two functional parts - the admin panel and the user panel.
Admin panel should contain the following views and tools:
- list of categories and the button of the command "New category";
- list of category parameters and buttons of commands "New parameter" and "Edit parameter";
- list of category components and buttons for commands "New component" and "Edit component".
On button click:
- "New Category" dialog box for creating a new category of components should be called, containing the lines for entering the name of the category in the view (visible name) and the name of the category for the database (only Latin plus an underscore);
- "New parameter" dialog box for creating a new parameter should be called, containing the lines for entering the name of the parameter in the view (visible name) and the name of the parameter for the database (only Latin plus underscore), the "Sublist Title" checkbox to indicate that the parameter is dummy and is not subject to filling in the database, and the drop-down list "Position in the list" with position numbers. By default, the position number in the list follows the last existing parameter;
- "Edit parameter" dialog box for editing a parameter should be called, containing a line for entering the parameter name in the view with a previously entered value;
- "New component" dialog box for creating a new component should be called, containing input lines for previously created component parameters. If the parameter is not filled in, then in the database this parameter should be recorded as "n / a" (no data);
- "Edit component" dialog box for editing the component should be called, containing lines for entering the parameters of the component with previously entered values. If the parameter is recorded in the database as "n/a", the parameter entry string must be empty.
The User Panel should contain the following views and tools:
- list of categories;
- a block of filters for components, consisting of input lines and/or drop-down lists of the main parameters (should be defined programmatically, at the code level);
- a list of components for the selected category that satisfies the filter conditions;
- list of parameters for the selected component;
- the button of the "Export to Excel" command, by clicking on which the list of parameters and parameter values for the selected component is exported to an Excel table.
Database requirements
Regarding this part, I have doubts about the correctness of the solution, and therefore I ask the respected community to confirm my choice or suggest another solution. Thank you in advance.
The database must use SQLite DBMS and contain the following tables:
- table of component categories components_category with fields id , name , view_name ;
- tables of component parameters by the number of categories parameters_<name> (where name is the content of the corresponding field of the table of component categories components_category ), with fields id , name , view_position , view_name , header ;
- tables of values of component parameters by the number of categories values_<name> (where name is the content of the corresponding field of the table of component categories components_category ), with fields id , <name1> , <name2> , … <nameN> (where name1 , name2 , … nameN are the contents of the corresponding name field of the parameter table components parameters_<name> ).
Я бы сделал базу в таком виде:
CREATE TABLE "components" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"name" TEXT
);
CREATE UNIQUE INDEX "index"
ON "components" ("name" ASC);
CREATE TABLE "parameters" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"components_id" INTEGER,
"name" TEXT NOT NULL,
FOREIGN KEY ("components_id") REFERENCES "components" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE TABLE "values" (
"id" INTEGER NOT NULL,
"vparameter_id" INTEGER,
"name" TEXT,
PRIMARY KEY ("id" ASC),
FOREIGN KEY ("vparameter_id") REFERENCES "parameters" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT
);
Не совсем понятно назначение поля view_position, но если сильно нужно, то его можно добавить в таблицу "parameters"
Если есть такая необходимость, то можно "ON DELETE RESTRICT" заменить на "ON DELETE CASCADE", тогда при удалении строки из "components" сразу удалятся соответствующие parameters и values
Вот эти строки поясните, для чего они.
Это поле нужно для хранения позиции параметра в представлении, поскольку может получиться так, что добавили ещё один параметр, но он должен располагаться выше ранее введённых параметров.
Т.е. то, что написано после FOREIGN KEY, относится к удалению из "components"? На данный момент удаления из "components" как такового не предполагается.
CREATE UNIQUE INDEX "index"
ON "components" ("name" ASC);
индекс на поле name, дополнительно делает поле name уникальным
| Т.е. то, что написано после FOREIGN KEY, относится к удалению из "components"? На данный момент удаления из "components" как такового не предполагается.
Так и есть, помимо этого FOREIGN KEY не дает содать parameters со значением components_id если нет соответствующего значения id в components. FOREIGN KEY обеспечивает целостность данных между 2 таблицами при добавлении и удалении данных
parameters с полем view_position будет выглядеть так:
CREATE TABLE "parameters" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"components_id" INTEGER,
"name" TEXT NOT NULL,
"view_position" INTEGER,
CONSTRAINT "fkey0" FOREIGN KEY ("components_id") REFERENCES "components" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT
);
А вот это для чего?