help for structure of an application

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
landrea
Posts: 27
Joined: 2018-08-04 13:09

help for structure of an application

Post by landrea » 2018-11-16 16:22

Hello, I need help in order to think how strcture a application.

What I need is create a table for offices and create a table for computers are in these offices

I want to know if there are computers not associate to the offices, so tables need to be separated and for this porpouse I see in the guide I can write sql to filter result of a table or write hook and get only computers don't have office.

I need probably also to think how dissociate a compuer is children of the offices table without delete it,
when I dissociate computer, it ends in the list of computer to associate in future or free that is part of the hook or filter I was speaking.

I have many doubts, but fisrt and more important doubt is if in this case I need a third table where i put together the two tables of computers and offices especially in order to assocaite and dissociate without delete really the compuer in the database (in case computer change office).

I need the same also for softwares and other things, but really I think I can adapt simple the concept to a lower level.

I'm personally oriented, but it's more a intuitiion rather than knowledge, to create a third table,
I hope someone can help me to chose right way

Thanks
Andrea

balfons
Veteran Member
Posts: 91
Joined: 2018-10-22 15:27

Re: help for structure of an application

Post by balfons » 2018-11-21 10:59

Hi,

It is not easy to determine the need of a concrete software without the complete requirements but as far as I understand:

1) You have two ledgers: computers and offices
2) Members of both ledgers can have relationships or not (it means a computer in spare assigned or not to an office).

I think you have 2 options:
sol 1. computer_table can have a field named office that will be non mandatory. In the cases of relationships it will be filled. In order to know which computers are assigned to any office you will use filters (non empty).
sol 2. relationships_table Each field will pair computers and offices. It could have date_from and date_to fields in order to control periods of validity of relationships and historical evolution of pairings. This is why I prefer solution 2.

I hope it helps

landrea
Posts: 27
Joined: 2018-08-04 13:09

Re: help for structure of an application

Post by landrea » 2018-11-22 13:48

Thanks balfons, surelly app gini is perfect for this application, since I don't have trilion of data, we speak of 200/250 computers and we need to fit the technical part of the law about privacy in europe (that unfortunatly it's the same for a corporation with 10.000 professionists and a no profit organization with no professinsits inside), I lack only of experience in this kind of project and I take a little time to think before start since it's more complicate change application if concept of application is totally wrong, I need to think the fileds and the relationshipes here.

I thought better about application and I think I would create only 2 tables for offices and computers (really they are more, computer and software, users, etc but I will use the same general idea)

In the table of office I create all the real offices and a fake office call "archive" or something similar

now, what happen is in case I need to move a computer(children of main table office) from a office to another or I need to add a computer we don't know immediatly what office assign, I can simple switch the "archive" option in my list.

In this way I can probably easy create a link in the main front page sending some parameters (for example I see you can save a filter research and add to the link the get parameters) and get all the computers dont' have a office for some reasons since they figure in this "archive" that is a sort of fake office I use for this porpouse.

It seems simple to do, I need only to think very well relationshipes and fields.

Probably I will give a look if is possible create a view where I join more tables, it's a function I'm not sure is in the guide or in the normal functions, join tables is something probably I need since I'm probably going to separe datas can be separated (as I read in theory about database and normalizations) otherwise I could get a foreign-father key ID if I export as excel or I print pdf, instead of a clear field and I will not understand withou check online all these single datass where are associated.

I need to think well application, it's not simple this phase, I probably will write down something in a paper for understanding what I need to do, surelly it's not clear the procedure in order to join table here and create a new view on fly, of course I can probably write hook, but pheraps it's not necessary for creating view between more tables, I think pheras filter again can do the same things.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: help for structure of an application

Post by jsetzer » 2018-11-22 14:25

After thinking about it for a moment, what do you think about following model as a starting point:

table "devicetypes"
  • id
  • name *
table "devices"
  • id
  • devicetype_id *
  • name *
  • ... more fields like serial-no, manufacturer, status_id, ...
table "offices"
  • id
  • name *
  • ...more fields like "organizationalunit_id", ...
table "device_offices"
  • id
  • device_id * (lookup --> devices.id)
  • office_id (lookup --> offices.id)
  • assigned_from (datetime)
  • assigned_till (datetime)
Example

Devicetype
id=1, name='computers'

Device
id=1, devicetype_id=1, name="PC-0001"
...

Offices
id=1, name="Office A"
id=2, name="Office B"

Device_offices
id=1, device_id=1, office_id=1, assigned_from=2018/01/01, assigned_till=2018/01/31
id=2, device_id=1, office_id=2, assigned_from=2018/02/01, assigned_till=2021/12/31
id=3, device_id=1, office_id=NULL, assigned_from=2022/01/01, assigned_till=9999/12/31

Having the "devicetypes" table you don't have to create separate tables for each technical-device type but just need one "devices" table. From my experience this normalized model will help you later on.

Just as a starting point, as mentioned in the beginning.

Regards,
Jan

PS: I would not name it "office" but try to find a more abstract term. Maybe some devices (machines) will be assigned to different places like "manufacturing hall", "construction site" or even associated with staff (like notebooks or tables) or "cost centers". There may be multiple assignments per device per period. This makes it more complicated, I know. But you should make up your mind in the development stage.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

landrea
Posts: 27
Joined: 2018-08-04 13:09

Re: help for structure of an application

Post by landrea » 2018-11-22 15:29

thanks jsetzer
I probably need to think careful application more than I thought,
your post help me

I was searching about join tables in the forum and in the main guide and there are not specific examples

I know how to write the sql, but I don't get how I will serve the data inside a table I didn't create inside appgini, if I add a custom page probably I need to add lot of things, create the html table and use the functons for save the data in pdf or excel, system use some libraries so I could probably call them and create a table, everything start to be more complicated than I thought

so, it's not clear if I need to join tables or not, pheraps if I think well I don't need at all, I need only in the final table lot fo datas autofiled by other tables that coincide with the pdf or exportation I need

here

table "device_offices"

id
device_id * (lookup --> devices.id)
office_id (lookup --> offices.id)
assigned_from (datetime)
assigned_till (datetime)

how can I get also the name of computer for example? I remember a video about autoflled lookup that pheraps is what i need

the id of the device is ok, but i want add other fileds are in the table or device and this of course automatically
the reason is simple because if I print I need to understand something, instead these ids mean nothing for who only will look the pdf

I think i will procede in this way
first I think and write down the main table with all datas I need to print, then i add look up fields and i separe the tables
I go in app gini and simple create everything at the contrary (starting from the single tables)

since i have also users and software is not simple think a main table get every data,
I probably will need at least two main tables, one for office (children computers and the user) and one for compuers (children sofwares)

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: help for structure of an application

Post by jsetzer » 2018-11-23 10:20

how can I get also the name of computer for example? I remember a video about autoflled lookup that pheraps is what i need
Your device_id field has to be a Lookup field.
  1. In AppGini, under device_offices select the device_id field
  2. Open Lookup field-tab
  3. Set Parent table = [devices]
  4. Set Parent caption field part 1 = [name]
You can do the same with device_offices.office_id field pointing to offices-table, showing caption [name].

Have a look at the Lookup-fields documentation which is a "must-read" from my point of view.

Regards,
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

landrea
Posts: 27
Joined: 2018-08-04 13:09

Re: help for structure of an application

Post by landrea » 2018-11-25 09:21

I pheraps need to have a fied in computer is foreigh key to offices and a field in office is foregn key to computers
so I need probably to change it automatically if something change and setup a read only field with jquery in one of the two tables

I though a way to join tables, source code is too complicated to get how work, these tables get 4 flies when you generate them from appgini

since I need to joint table only to print or export, i dont' need crud functions here and pheraps is also better don't use here, I will simple add custom pages and use sql to get ta datas from two or more tables, I use datatables plugin for jquery that is a framework I know quite well.

style is probably not exactly the same, but since they can work with boostramp, is probably not totally different

In this way I need to think the application, but I don't need to know everything I will need (that I don't know) since if I need something (that is probably a sum of values are in different tables) I will use these custom pages to print or export all datas I need and I dont have to change continuosly the structure of database

Post Reply