Jump to content

copying data from one table to another


jimfog
 Share

Recommended Posts

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by birbal
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by jimfog
Link to comment
Share on other sites

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 by jimfog
Link to comment
Share on other sites

 

 

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by dsonesuk
Link to comment
Share on other sites

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 by jimfog
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...