Creating a table for SQL of users

Ignition
1

Seems like tables with first and last names is a common thing people make, and I want to avoid common mistakes.

I think varchar(80) for user names seems good.
Looks like industry standard varies from 60-300 characters from my research.

I remember vaguely having an issue with unentered data with one of the datatypes though, and I don’t remember which one it is.

Also, some people are giving me just last names. I was going to put last and first name in one column.

Right now I am just showing these names in one column on a report.

2

I’m sorry but… What’s the question ? :smiley:

1 Like
3

haha
Is varchar(80) for one column of (lastname, firstname) an obvious mistake?

4

You’re putting first name and last name in the same column ?
I’d keep them in separate columns, and allow null for the first name if some people don’t provide it.
80 should be fine for each one of them, but frankly I’m just assuming…

5

when you say allow null, what do you mean?

6

When you set up the column, set it so that NULL values are allowed.

You can set up a column to either allow NULL or not. If it doesn’t and you try to insert or update a NULL value you will get a SQL Exception.

7

Isn’t allow NULL default for most databases?

8

I have nulls all the time on the user input stuff and sometimes even the transaction groups

I hope it is default, or I really need to know how to set null allowed

9

It is default for most databases that I am aware of, and if you’re adding a column to a table that already has data in it, I don’t think you can do it any other way.

I know for a fact it is the default for MS SQL.

1 Like
10

You should take a look at Falsehoods programmers believe about names and then go cry in a corner when you realize how inadequate your planned system will be.

(But then feel a bit smug because you are not having to deal with Falsehoods programmers believe about addresses)

3 Likes
11

Thanks

I very much appreciate the perspective

I am going to accept that this column will have issues and I will deal with them as they crop up

12

One thing to consider as a possibility is to create an Ignition User Source of Database type and point it to your database connection (probably make sure to customize the default prefix of scada_ to your needs). Then add a user through the Gateway web UI, and Ignition will automatically create tables like below (example is MariaDB):

2022-04-05 at 9.12 AM
2022-04-05 at 9.12 AM822×716 58.9 KB

One advantage to this is that you could use the Ignition functions (and components) for managing the user source. Just something to consider (if it happens to fit the need).

4 Likes