^Bart wrote:

I need to add customers, companies and users to a db, I thought to
create a single table with customers and companies named registries
where when I add a user I don't add vat and vice-versa when I add a
company I don't add name and surname!

What happens when a "user" is also a "customer"? How about situations where
the Company may have the same name as the User (as in "Sole
Proprietorships", etc.)? How do you intend to distinguish between these
various dualities?

I need to use a third table for login feature named users but I don't
know if I should link it to the registries!

I thought to add in users table username email and password, I thought
to store all other details in the registries table.

What you'd like to do?

--
Lew Pitcher
"In Skills, We Trust"

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

I need to add customers, companies and users to a db, I thought to
create a single table with customers and companies named registries
where when I add a user I don't add vat and vice-versa when I add a
company I don't add name and surname!

I need to use a third table for login feature named users but I don't
know if I should link it to the registries!

I thought to add in users table username email and password, I thought
to store all other details in the registries table.

What you'd like to do?

^Bart

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 24/01/2020 22.35, ^Bart wrote:

I need to add customers, companies and users to a db, I thought to
create a single table with customers and companies named registries
where when I add a user I don't add vat and vice-versa when I add a
company I don't add name and surname!

Best practice is that you do have the company in it's own table, it will
just be a grouping for users, users will belong to a company or not. The
user table can have the credentials, this will allows you to have a
company with multiple users and with different privileges, as the
accountant do not need the privileges to change the content of the pizza
while the bake wouldn't need to see the invoices or order extra services.

Say someone would get the credential for one user, they will be able to
do less harm when not having full access to everything and also the
password would be not shared at the company as the account would be
personal. You would also know who has lost it's credentials, which can
help in the investigation how someone got hold of the credentials.

In theory a person could be connected to more than one company, but
seldom happens, so it's up to you if you want a one to one connection
from user to company or an one to multi connection.

Users may have a "admin" flag telling if they have the right to request
changes to company user setups.

The company table should of course have all the information for the
company, including an invoice address (email or physical).


--

//Aho

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 25/01/2020 08:54, J.O. Aho wrote:

Best practice is that you do have the company in it's own table, it will
just be a grouping for users, users will belong to a company or not.

Many companies have more than one location.

You need a one to many relationship between the company and 'places'



--
No Apple devices were knowingly used in the preparation of this post.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 25/01/2020 12.22, The Natural Philosopher wrote:

On 25/01/2020 08:54, J.O. Aho wrote:

Best practice is that you do have the company in it's own table, it
will just be a grouping for users, users will belong to a company or not.

Many companies have more than one location.

You need a one to many relationship between the company and 'places'

A company do just have one HQ, most likely one billing address (sure
some companies may have different departments to pay for their services,
but I doubt that will be the case here), sure a company can have more
than one visiting location, but that you handle in another table.

--

//Aho

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 24/01/2020 21:35, ^Bart wrote:

I need to add customers, companies and users to a db, I thought to
create a single table with customers and companies named registries
where when I add a user I don't add vat and vice-versa when I add a
company I don't add name and surname!

I need to use a third table for login feature named users but I don't
know if I should link it to the registries!

I thought to add in users table username email and password, I thought
to store all other details in the registries table.

What you'd like to do?

depends. I wouldnt mix customers and companies.

What I ended up doing was having a 'places' table and a 'people' table
So you could have more than one person in a place. And a place might be
a customer or a supplier or a marekting propsective customer. Etc.

Then using link tables to associate them. So if a place appeared in a
table of customers suppliers and marketing propsects it would mean that
you had bought from a conpany, sold to it and were trying to sell to it.


Customers might also be people who work for companies that supply you,
or indeed they might be companies themselves.

By going properly relational its more complex, but the edge cases can be
dealt with .

Think carefully about what information is unique to a person and if its
one to one. A person doesnt have several names, but they may have
several phone numbers and several email addresses and they may live in
one place and work in another.

So you may need a one-to-many repalstuonship of people to places and
phone numbers and email addresses

The biggest part of a business analyst trying to model this is software
is to map out and understand these relationships. The next hardest task
is to define the table structires.

The easy part is writing the code to drive the databases

^Bart

--
The theory of Communism may be summed up in one sentence: Abolish all
private property.

Karl Marx

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Thank you for your reply! :)

Best practice is that you do have the company in it's own table, it will
just be a grouping for users, users will belong to a company or not. The
user table can have the credentials, this will allows you to have a
company with multiple users and with different privileges, as the
accountant do not need the privileges to change the content of the pizza while the bake wouldn't need to see the invoices or order extra services.

Yes this is really true, 99% of users are linked to a company, it could
happen to have an user who can be able to see more than a company but it
could have a 1% of users who don't have a company (end user!).

Now I should think how many tables I should create, I thought to create:

companies
-----------------------
company_id
name

companyplaces
--------------------------
companyplace_id
placename
placerole ENUM (warehouse, office, warehouseoffice)
company_id_fk

userrules
----------------------------
userrule_id
name (admin, user, superuser)

users
------------------
user_id
name
surname
username
email
userrule_id_fk

companygroups
----------------------------
companygroup_id
company_id_fk
user_id_fk

I create companygroups table because a user could have more than one
company but it could be possibile (1%) he could be an enduser and he
could be able to see other tables but not company's table!

Say someone would get the credential for one user, they will be able to
do less harm when not having full access to everything and also the
password would be not shared at the company as the account would be
personal. You would also know who has lost it's credentials, which can
help in the investigation how someone got hold of the credentials.

Yes, this is a good a idea, password must be personal and should be to
the user level and not to the company level.

In theory a person could be connected to more than one company, but
seldom happens, so it's up to you if you want a one to one connection
from user to company or an one to multi connection.

I work for a company but in the same place we have another company and
someone (like me) is able to see things of both companies, it could
happen also in other companies and I'd like to think about this case,
one to multi connection!

Users may have a "admin" flag telling if they have the right to request changes to company user setups.

I thought to it like what I wrote above, maybe I could do it better than
how I wrote! :)

The company table should of course have all the information for the
company, including an invoice address (email or physical).

Yes of course, I thought in the above schema to have another table when
I could have more than a place of the same company.

^Bart

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Many companies have more than one location.

You need a one to many relationship between the company and 'places'

This is true, for example a company could have warehouses in different
places or offices in different places, now I have one to one from
company to place and I should think to have, like what you wrote, one to
many and also to write in a specific place if there's just a warehouse
or also an office.

Regards.
^Bart

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

depends. I wouldnt mix customers and companies.

I thought it was more easy to have one table but now I think it's
impossible to manage every case of study just with one table with both!

What I ended up doing was having a 'places' table and a 'people' table
So you could have more than one person in a place. And a place might be
a customer or a supplier or a marekting propsective customer. Etc.

Good idea! :)

Then using link tables to associate them. So if a place appeared in a
table of customers suppliers and marketing propsects it would mean that
you had bought from a conpany, sold to it and were trying to sell to it.

Customers might also be people who work for companies that supply you,
or indeed they might be companies themselves.

Yes, a company could be for another company a customer or someone which
sell products!

[CUT]

Think carefully about what information is unique to a person and if its
one to one. A person doesnt have several names, but they may have
several phone numbers and several email addresses and they may live in
one place and work in another.

And a person could work for a company but he could be able to see/manage
other companies.

So you may need a one-to-many repalstuonship of people to places and
phone numbers and email addresses

I understood!

The biggest part of a business analyst trying to model this is software
is to map out and understand these relationships. The next hardest task
is to define the table structires.

The easy part is writing the code to drive the databases

Golden words! :)

^Bart

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 25/01/2020 11:43, J.O. Aho wrote:

On 25/01/2020 12.22, The Natural Philosopher wrote:

On 25/01/2020 08:54, J.O. Aho wrote:

Best practice is that you do have the company in it's own table, it
will just be a grouping for users, users will belong to a company or
not.

Many companies have more than one location.

You need a one to many relationship between the company and 'places'

A company do just have one HQ, most likely one billing address (sure
some companies may have different departments to pay for their services,
but I doubt that will be the case here), sure a company can have more
than one visiting location, but that you handle in another table.

You may know how to code, but you dont have much experience of
multinational companies.

There may be a notional head offioce sited somehere for tax reasons, but
the daughter companies and divisions can be extremely geographically
diverse.



--
“But what a weak barrier is truth when it stands in the way of an hypothesis!”

Mary Wollstonecraft

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 25/01/2020 20.06, The Natural Philosopher wrote:

On 25/01/2020 11:43, J.O. Aho wrote:

On 25/01/2020 12.22, The Natural Philosopher wrote:

On 25/01/2020 08:54, J.O. Aho wrote:

Best practice is that you do have the company in it's own table, it
will just be a grouping for users, users will belong to a company or
not.

Many companies have more than one location.

You need a one to many relationship between the company and 'places'

A company do just have one HQ, most likely one billing address (sure
some companies may have different departments to pay for their
services, but I doubt that will be the case here), sure a company can
have more than one visiting location, but that you handle in another
table.

You may know how to code, but you dont have much experience of
multinational companies.

There may be a notional head offioce sited somehere for tax reasons, but
the daughter companies and divisions can be extremely geographically
diverse.

Daughter companies are separated from the mother company, they have for
example different tax/organization id's and a contract with A don't mean
you have a contract with B. If you have A and B, they should be treated
as independent companies in the system.

Company with divisions spread over a large area in a country and each
division pays for the service, it may be better to have them as
"separated" companies, this makes it easier to keep track of who has
pied and who hasn't and who is allowed to take decisions for that
division, you still have the same tax/organization id to join them on.

--

//Aho

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 25/01/2020 12.59, ^Bart wrote:

Thank you for your reply! :)

Best practice is that you do have the company in it's own table, it
will just be a grouping for users, users will belong to a company or
not. The user table can have the credentials, this will allows you to
have a company with multiple users and with different privileges, as
the accountant do not need the privileges to change the content of the
pizza while the bake wouldn't need to see the invoices or order extra
services.

Yes this is really true, 99% of users are linked to a company, it could happen to have an user who can be able to see more than a company but it could have a 1% of users who don't have a company (end user!).

Now I should think how many tables I should create, I thought to create:

companies
-----------------------
company_id
name

tax/organization id's? No need to duplicate those.

companyplaces
--------------------------
companyplace_id
placename
placerole ENUM (warehouse, office, warehouseoffice)
company_id_fk

^^^^--- keep the same name on the column as in the table where it's a
primary key.

userrules
----------------------------
userrule_id
name (admin, user, superuser)

You shouldn't make defined roles, you should dived things up in
permissions, say "create recipes", "update recipes", then you have a
roles table with a role name (kind of your userrules) and a join table
where you say what privileges belongs to which role.

You will also need a privileges table where you tell which permissions
(as you may want to give more than just the ones they have for a role)

and last you need a user role table which tells which roles each user has.

This gives you the most flexibility, you can change what an admin can do
or not do without need to recode things, it's just a configuration.


--

//Aho

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

companies
-----------------------
company_id
name

tax/organization id's? No need to duplicate those.

In this case I'd like to start "just" with a pure warehouse management
software I don't need to add taxes feature, I create a companies table
where I store the "mother company" and its real name, in the
companyplaces table I thought to store the local brand (local phone,
local picture, etc.) connected to the "real mother" company!

companyplaces
--------------------------
companyplace_id
placename
placerole ENUM (warehouse, office, warehouseoffice)
company_id_fk

^^^^--- keep the same name on the column as in the table where it's a
primary key.

I thought to store in the companies table the name of the company like
Coke and in companyplaces I'll store every place and for every place
where I have a Coke office and I'll be able to add local phone, etc.

userrules
----------------------------
userrule_id
name (admin, user, superuser)

You shouldn't make defined roles, you should dived things up in
permissions, say "create recipes", "update recipes", then you have a
roles table with a role name (kind of your userrules) and a join table
where you say what privileges belongs to which role.

Something like:

userrules
------------------
userule_id
name (Manage products, Manage companies, Manage places)

roles
-------------
role_id
name (Products manager, Brand manager, etc.)
userrule_id

You will also need a privileges table where you tell which permissions
(as you may want to give more than just the ones they have for a role)

privileges
-----------------------
privilege_id
name (Read only, Read&Write, etc.)

and last you need a user role table which tells which roles each user has.

Mhmm... I don't understand this fourth table because I don't know what I
could add more than three tables... :\

This gives you the most flexibility, you can change what an admin can do
or not do without need to recode things, it's just a configuration.

Sure but... in your mind maybe you thought to four table but I didn't understand all of them...

^Bart

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)