copying data from one table to another

[jimfog]

I have 3 tables...one that holds credentials of the users(password, email etc).

 

A 2nd table where the name, lastname of the regular_users are kept and 3rd table where the name,lastname of the business users are kept.

 

I want to do this:

 

Suppose the business user chooses to close his account as a business user but retain him only as a regular user.

 

Is is possible to transfer the name,lastname to the regular user table and associate these with credentials already found in the credentials table?

[dsonesuk]

Would it not be better in not having two tables store more or less the same data, but have single table and have a field to identify him as regular or business user instead. Then you just change that field from one or the other.

[jimfog]

the regular_users table holds name and last name while the business user table holds not only the name and lastname but much more data such as address, url etc.

 

So I am not sure is good to do what you are suggesting

[birbal]

Use a table as base 'regular user' table and use another table for business user where there will be a foreign key pointing to regular user. If a use chooses to close his business identity just let them delete the data from business user table, whereas there will be still data for him as regular user in corresponding table. You should avoid duplicate data where possible and use reference instead.

Edited August 27, 2013 by birbal

[jimfog]

so you are saying that in the regular users table there will ALSO be the name and last name of the business users.

 

Currently the name and last name of the business users is contained in the business users table.

 

I think what you suggest messes things a little.

[jimfog]

I finally concluded in the following logic which cannot be implemented though due to foreign key constraint issue.

Here is what I am trying to achieve:

 

1.First "take" from the business users table the crID(primary key)m, the name and last name.

2.Delete the b.user account.

3.Transfer the data from the first step to the regular users table

 

The problem is that the above violates a foreign key constraint, a foreign key which goes from the credentials table(parent table, it holds the username and password and crID which is the primary key) to the regular users table(child table which holds name and lastname)

 

What can I do?

[justsomeguy]

It doesn't make sense to me to store names in different places depending on what kind of user they are, I wouldn't design the database like that. I would have one table that holds all of the common data for any user, like username, password, name, email address, user type, etc, and use other tables to hold data that is specific to a certain type of user. I don't see a reason for one table that holds username and password and another table that holds the name for a regular user, those should be in the same table. If you set it up like that then you wouldn't have the issue of moving data, if the user changed types then you would just delete the data that applied to the old type and change what type they are.

[jimfog]

Having 2 tables(regular user and b. user) that hold the same columns(name and last name) do you think it is bad also from a duplicate data perspective?

 

And if yes, why is it so bad having duplicate data in a database?

[thescientist]

https://en.wikipedia.org/wiki/Database_normalization

http://www.guru99.com/database-normalization.html

Edited August 27, 2013 by thescientist

[birbal]

No, i did not mean to have common data in both table. I said to use foreign key which is used to avoid data duplication. I meant exactly what justsomeguy just elaborated.

[jimfog]

So...the bottom line is that having two tables(regular users and business users) that have same attributes(name and last name) is wrong.

 

Anyway I am going to redesign the table some moment in the future.

For now, and in order to deal with the issue mentioned in the beginning of the topic I just foreign_key_checks to 0, so that the query can be performed.

 

it is just a temporary solution.

Edited August 28, 2013 by jimfog

[jimfog]

Would it not be better in not having two tables store more or less the same data, but have single table and have a field to identify him as regular or business user instead. Then you just change that field from one or the other.

 

What type of column would be that the will identify a user from being a regular or business user? I do no think it should be enum though because we are talking here only about two possible values

 

Use a table as base 'regular user' table and use another table for business user where there will be a foreign key pointing to regular user. If a use chooses to close his business identity just let them delete the data from business user table, whereas there will be still data for him as regular user in corresponding table. You should avoid duplicate data where possible and use reference instead.

Are you saying that this "regular users" table will hold data common for the 2 types of users and the business users table will hold only data relevant to the

business user?

Edited August 29, 2013 by jimfog

[justsomeguy]

 

 

What type of column would be that the will identify a user from being a regular or business user? I do no think it should be enum though because we are talking here only about two possible values

If there are only ever 2 values then I use a 1 digit tinyint (i.e., a bit), or else I use an enum.

 

 

Are you saying that this "regular users" table will hold data common for the 2 types of users and the business users table will hold only data relevant to thebusiness user?

Right.

[jimfog]

If there are only ever 2 values then I use a 1 digit tinyint (i.e., a bit), or else I use an enum.

 

I assume it will be a separate table...the one with the enum values....and it will have a foreign key referencing the user(regular users business users) table.

 

Concluding, the only thing that worries is the fact that much code now must be revised in order to be adjusted to the new db design model proposed.

[justsomeguy]

Enum is a column type, not a separate table.

[jimfog]

I know that enum is a column type but since enum is of the integer type there must be a table which will say

that for example, a value of 1 means regular user and value of 2 means business user.

 

And in the users table there will be a column that will have a value of 1 or 2 depending the type of user we are dealing with.

 

Do you agree?

[dsonesuk]

Yes!, you can create another table which will hold the relative id ref and another column describing type of user, but you could, as you only have two, just write php code that would show a description depending on value retrieved. AND even mysql functions such as CASE, which will give result depending on column value, this result is then applied to an alias field name.

[jimfog]

Yes!, you can create another table which will hold the relative id ref and another column describing type of user, but you could, as you only have two, just write php code that would show a description depending on value retrieved. AND even mysql functions such as CASE, which will give result depending on column value, this result is then applied to an alias field name.

When you say "relative id ref" I suppose you do not imply the existence of a foreign key cause, that is not needed here(if I am correct).

 

You also mention something PHP code. Does that mean that for two values I might not need a table at all-how exactly I am going to distinguish the users, give an example.

The db MUST store info regarding the type of users

[dsonesuk]

Yes, id_ref or better still 'user_type_id' as to help apply a description to go with user_type_id such as 'regular' or 'business', in a new table would be the foreign key

 

 

user_type_id | user_type_descrip

1 | regular

2 | business

 

to relate to a primary key use main_user_type_id in main table

 

||main_name | main_user_type_id ||

||tom | 1 || would be regular

||bill | 2 || would be business

||harry | 1 || would be regular

 

feel such a ######, should have known ###### would be treated as swear word and blocked out! so used Bill instead of, wait for it D_I_C_K

Edited August 31, 2013 by dsonesuk

[jimfog]

Yes, id_ref or better still 'user_type_id' as to help apply a description to go with user_type_id such as 'regular' or 'business', in a new table would be the foreign key

 

 

user_type_id | user_type_descrip

1 | regular

2 | business

 

to relate to a primary key use main_user_type_id in main table

 

||main_name | main_user_type_id ||

||tom | 1 || would be regular

||bill | 2 || would be business

||harry | 1 || would be regular

 

feel such a ######, should have known ###### would be treated as swear word and blocked out! so used Bill instead of, wait for it D_I_C_K

So, the table below(the one with main names) will reference the table with user_types(from main_user_type_id column-to-user_type_id column)

Edited September 1, 2013 by jimfog

[dsonesuk]

Yes! you then use JOIN or LEFT JOIN (not required if every user will be either have reg id ref, or business id ref, and not empty) to bring them together in SQL query

[justsomeguy]

I know that enum is a column type but since enum is of the integer type there must be a table which will saythat for example, a value of 1 means regular user and value of 2 means business user.

Enum is not the same as an integer column. The values can be anything, they don't need to be integers. The storage engine will only use 1 byte to store an enum value, unless you have several hundred possible values (then it will use 2 bytes). The only other things that only use 1 byte for storage are tinyint, char(1), binary(1), bit(1), or set. You can use "regular" and "business" as values that you store, and it's still only going to need 1 byte. You can add several other types of users and give them meaningful names and it's still only going to use 1 byte to store the values. It uses an internal symbol table to do the translation, it's like a lookup table that the database handles internally. Enum is the preferred column type whenever you have a finite list of options for a particular column. It will use less space than other column types. If you only have 2 possible options, and will never have more than that (like whether or not a user is active), then a 1-byte tinyint works fine for storing a 1 or 0.