Recent

Author Topic: setup of database and tables (newbee question)  (Read 5658 times)

Arnoldg

  • Newbie
  • Posts: 1
setup of database and tables (newbee question)
« 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


egsuh

  • Hero Member
  • *****
  • Posts: 1266
Re: setup of database and tables (newbee question)
« Reply #1 on: June 12, 2021, 04:20:38 pm »
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.

devEric69

  • Hero Member
  • *****
  • Posts: 648
Re: setup of database and tables (newbee question)
« Reply #2 on: June 13, 2021, 02:28:23 am »
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)
« Last Edit: June 14, 2021, 10:55:52 am by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: setup of database and tables (newbee question)
« Reply #3 on: June 14, 2021, 08:50:48 am »
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.
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

af0815

  • Hero Member
  • *****
  • Posts: 1284
Re: setup of database and tables (newbee question)
« Reply #4 on: June 14, 2021, 01:11:11 pm »
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.
regards
Andreas

engkin

  • Hero Member
  • *****
  • Posts: 3112
Re: setup of database and tables (newbee question)
« Reply #5 on: June 14, 2021, 01:36:08 pm »
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.

af0815

  • Hero Member
  • *****
  • Posts: 1284
Re: setup of database and tables (newbee question)
« Reply #6 on: June 14, 2021, 02:30:04 pm »
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

 
regards
Andreas

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: setup of database and tables (newbee question)
« Reply #7 on: June 14, 2021, 02:54:17 pm »
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
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

engkin

  • Hero Member
  • *****
  • Posts: 3112
Re: setup of database and tables (newbee question)
« Reply #8 on: June 14, 2021, 04:00:50 pm »
@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.

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: setup of database and tables (newbee question)
« Reply #9 on: June 14, 2021, 04:16:22 pm »
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.......
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

engkin

  • Hero Member
  • *****
  • Posts: 3112
Re: setup of database and tables (newbee question)
« Reply #10 on: June 14, 2021, 06:22:35 pm »
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!

 

TinyPortal © 2005-2018