Lazarus
Programming => Databases => Topic started by: Arnoldg on June 12, 2021, 02:58:25 pm
-
Hello,
I'm trying to setup a new database with tables and that kind off stuff.
now is my question, i have a lot of different tables with the same kind of information.
example:
table1=firecompany:
ID
Name
Adres
Postal
City
Certification number
table2:manufactor:
ID
Name
Adres
Postal
City
table3:subcontractor
ID
Name
Adres
Postal
City
What i think the best way to do this is to make 4 tables like this:
table1: information
ID_information
Name
Adres
Postal
City
table2: firecompany
ID
ID_information
Certification number
table3 manufactor:
ID
ID_information
table4 subcontractor:
ID
ID_information
is this the right way if knowing that a manufactor an also be a subcontractor.
If this is the right way how to connect the tables to gether and reprecent the data in lazarus IDE
-
Relational database is a separate area from Lazarus. You need to study some basic stuff on relational database, and SQL statements. Then try to write applications dealing with databases with Lazarus.
-
Hello @Arnoldg,
First, choose your database:
- is it for a desktop-only program (locking the full tables like with Paradox or dBase)?
- is it with several clients and a centralized server (and possibility to lock only some records of each table like with mariaDB or fireBird), ...?
Then study the choosen SQL engine (primary versus foreign keys, how to make relationships between tables), then choose a SQL manager in order to make the SQL statements \ DDL queries, and to create your database. Only then, you will code with Lazarus (and its components like TDatamodule, TConnexion, TDataset, TQuery, ..., TDataSource, TDBedit, TDBGrid, ..., TForm).
Here are some SQL managers:
- DB browser for SQLite (SQLite)
- FlameRobin or RedExpert (Firebird)
- MyDbfStudio (dBase)
- PhpMyAdmin (mySQL\mariaBD)
- the project .../lazarus/tools/lazdatadesktop (a little bit of everything, like the "Database Desktop" of Delphi)
-
What the others said not withstanding:
I'd rather go like this
Table1 - tbl_addresses (or whatever name)
ID
Category_ID
Name
Address
PostalCode
City
WhateverOtherFields
Table2 - tbl_category
ID
Description
Description in table 2 would be like "FireCompany" etc.
-
It is no Problem to create tables at runtime, but it can differ on the DB you use. The simplest way is to do it on a SQL based DB.
-
For tables, I would go with Zvoni. I would also do to cities what he did to categories, a separate table. But, since one company can belong to more than one category, you can add more fields for other categories, or you can use two tables instead of one. The first table is like tbl_category in Zvoni's suggestion, and the second table is to create a link between a company and a category.
-
table2:manufactor:
ID
ID_Parent -> Default NULL
Name
Adres
Postal
City
table3:subcontractor
If you insert a field ID_Parent in the Tabel you can make a hierachial query see https://www.linkedin.com/pulse/step-by-step-guide-creating-sql-hierarchical-queries-bibhas-mitra or https://learnsql.com/blog/how-to-query-hierarchical-data/ ,... for more information
-
For tables, I would go with Zvoni. I would also do to cities what he did to categories, a separate table. But, since one company can belong to more than one category, you can add more fields for other categories, or you can use two tables instead of one. The first table is like tbl_category in Zvoni's suggestion, and the second table is to create a link between a company and a category.
@Categories: Agreed. In that case it would be a classic "m:m"-scenario
@cities: Arrggg..... yes, you can "normalize" a database-design ad nauseum, and i remember doing this with Addresses, where i separated the City into a separate table (basically a simple "1:m"-relation)
Bottom line: Not really worth the hassle, except if we're talking millions of rows
-
@cities: Arrggg..... yes, you can "normalize" a database-design ad nauseum, and i remember doing this with Addresses, where i separated the City into a separate table (basically a simple "1:m"-relation)
Bottom line: Not really worth the hassle, except if we're talking millions of rows
Yes, it depends on the number of rows. In reality, the address should be in a separate table to account for same company with different addresses (branches) in the same city and/or different cities.
With a few rows, one table is good enough.
-
engkin,
i once had a talk with a (amateur) developer, and he told me what happend to him:
Yes, he had the cities in a separate table, with the person/address-table having a Foreign_key to the Cities-table (tbl_city being the Master) (Something like an AddressBook)
He overlooked/missed that his Foreign-Key had ON DELETE CASCADE (since he copied some columns from a template-table)
Have a guess what happenend, when a user couldn't find the Edit-Menu-Entry (because he had found a typo in the City-Name), and decided: What the hell? Let's delete it, and enter it anew.......
-
What could go wrong!
Developer:
Let's copy things--->that was fast
User:
a typo-->cannot find the edit menu--->let us delete it--->Problem solved!