Database use - What's the best way?

[Lutzilutz]

I’m trying to set up a database for my small company. I have a hard time figuring out how to do what I want.

I have a table which contains all equipment we own. Each entry is a different piece of equipment. This table will be in constant change (old equipment needs trashing, buying new things, …).

On the other hand, for each new contract we need to set a list of equipments we will be using to be able to give the contractor an esimation of the price. This list should contain all equipement we have (could just be an unique ID) but also a boolean to record what we use and what we don’t.

I can’t find a user-friendly way of doing it (I won’t be the one generating the estimations). Here are my questions :

• How am I supposed to save all these material lists + boolean for each contract ? What would be the good way of doing it ?
• Does it make sense to have a new table for each new contract ? Shouldn’t table be only created once and populated ?
• Are macros the only way of achieving that ? (automatic duplication and rename of a table)

And here are my requirement :

• Creating a new estimation should be user-friendly (list of equipment + yes/no)

I hope someone can give me a vague idea of the direction I should be taking. My understanding is that any new equipment entry (row) should create a new field (column) in all future estimations. It feels wrong to me (plus I have no idea how I can do that). Thanks to all !

[Villeroy]

Database development is a well paid profession.

For the backend you need the structured query language (SQL) and some theoretical concepts. If the backend should be accessible from many clients or from the internet, you need knowledge about computer networks.

Nowadays, a database frontend might be some kind of web page (this web page for instance). This office suite’s database functionality is a reminder of the late 90ies when single-user desktop databases were distributed on CD-ROMs and printing information on paper was essential. Base is far too simple to be used “intuitively” by novices. For any database professional, Base is a cheap and over-simplified frontend. It is very well suited to serve data from already existing databases to office documents. It provides input forms in the 90ies style, fairly well suited to edit interrelated table data (one-to-one, one-to-many, many-to-many relations). Macros are a no-go. If you need macros to work with your database, the database is poorly designed.

Your problem is the “learning by doing” approach without knowing what to do. You can not design any database without a minimum of theoretical concepts.

P.S. yesterday’s topic answering basically the same question based on the description of a properly designed many-to-many relation: How to choose a value for a field from a related table?

[ajlittoz]

Obviously from your question, you need to learn a bit about relational databases. The chore point is the structure of the database. To oversimplify it, you have “primitive assets”: the equipment you own, your working relationships (customers, clients). Every time you project a deal, you create a contract (another type of table) which is a one-to-many relation from customer table to contract table. The contract needs equipment: this is yet another one-to-many relationship from contract to equipment.

Every one-to-many relationship item is stored in an intermediate table where each record contains originator id and “destination” id. These intermediate records play the role of your boolean: if there is such a record, the equipment piece is needed (true); if it does not exist, the piece is not needed (false).

As @Villeroy says, if you aren’t “fluent” with SQL (and, beware, there are as many slightly different variants as database engines), you’d better ask a skilled professional because a business application can’t tolerate errors or approximations lest the business closes.

[Lutzilutz]

Thanks to both @Villeroy and @ajlittoz ! I had a basic university class of relational databases but I underestimated the need to think my database longer. My goal is to setup a basic database to start, and later indeed work with a professional.

@Villeroy I understand now why Base design and templates look so 90’s. Thanks for all that condensed tips and summary, super useful and appreciated ! Awesome.

@ajlittoz You cleared the main thing that was blocking me (boolean VS intermediate records) so thanks a lot !

I’ll put you both as solution if I can.