> When I first setup my database, I knew nothing about Database
> Now, I have 100,000 rows in a table that is cluttered beyond belief.
> just some of the data:
Unfortunately, that's typical of DBs that aren't normalized. IMO,
understanding efficient design goes a lot further than anything else you can
learn when working with DBs.
> member_id, first_name, last_name, username, password, street1, street2,
> city_st_zip, country, last_update, last_login, (4 more fields here),
> there's even more than that, but I'll spare the details =) Trouble is, a
> couple of these columns get updated regularly, and the whole table is
> locked during these times. Not to mention the fact that the DB is not
> normalized. So, I guess I have a few Q's:
> *) If I wanted to take 4 colums OUT of the member's table, and add a new
> table, say "mailing_info", how would I do this? The query would have to be
> quick, as I'd have to shutdown access to the site to do so, then quick
> update my scripts. Of course, the "member_id" field would have to be added
> to the mailing_info table as well.
First of all, in this new table you should make sure that you break out the
field that you have labeled as "city_st_zip" into three different fields in
order to meet normalization standards. Naturally, you would use the Member
ID as the foreign key to your existing members table. Having said that,
depending on your data, it may not be a quick and easy query to break out
this info. Do you have a consistent delimiter for the three items in this
one field? If not, you will have trouble with making sure that each one of
the three items is put into the correct place in the new table.
> *) How should the tables be setup so the db is normalized? I.E., should
> there be one table just for "logins", then I pull the last login from that
> table? Or should there be a separate table with member_id, last_update,
Do a search for "normalization" and you should find a list of the rules you
should follow. I think that your question above relates more to your DB
locking problem than normalization. It would certainly help to split your
data among different tables to prevent the locking problem you mentioned,
but this isn't always the most efficient way to do it, again, depending on
your data and current design.
Beridney Computer Services & Beridney Talent