Printable View
Hi,
We are developing an online application that will, among many other things, be taking a user's mailing and billing addresses.
I was wondering, which is better:
1) storing both addresses in the same table as the user account
2) creating a seperate table to store just addresses (and user_id)
I realize that one may be better depending on the situation, but what are the drawbacks of doing it either way?
Oh, and we are using MySQL 4.1.11 with InnoDB tables.
Thanks
Creating a separate table is better from a design point of view. Make the user account the primary key in the first table (containing user account, first name, last name, whatever), and foreign key in the second table (containing user account, mailing address).
The draw-back of not doing it that way is that you'll end up with a bunch of redundant data, which may (and probably will) lead to data integrity problems.
#1 -- If you need more addresses per user, this solution won't work without rebuilding the database and then possibly wasting space if you can't make use of all of the addresses.
#2 -- Lets you have as many (or as few) addresses per user as you need, but you need to perform an extra query to do so.
That is what I see. I don't know much about other potential issues in SQL (and the different tables) from those options though.
If the database is going to hold things like purchases or things that need a unique primary key, like customer order ID .... it is better to have a saperate table .... as the primary key in the first table would be customer name {Some prefer customer ID}.... and the second table would has the customer order ID as primary {Becasue it is really trivia to take into consideration that one customer may conduct more than one purchase if you did not apply that point the database won't let a customer purchase more than once}.
Refering to the above situatio, a one-to-many relationship should take place on order to link customer purchasments and customer names and addresses .....
Otherwise, having two tables would be really not feasible .... becasue one table can hold as many information as you want ....
If one of the two addresses is going to be dealt with differently or in another online applications it is recommended to have two tables ..... But remember to implement the right relationship .... otherwise you would killing your application ....
Remember the more tables and fields you have, the more sapce you will be occupying ....
I hope I could help you in a way or another .....
Storing database in the same table is hell risky. I have suffered once, losing some 500 users for my website :( , Still I couldn't recover them cause, then I was noob and didn't even have back up.
How on earth can you store a database in a table?? :confused:Quote:
Originally posted here by linux1880
Storing database in the same table is hell risky. I have suffered once, losing some 500 users for my website :( , Still I couldn't recover them cause, then I was noob and didn't even have back up.
Define a database!
Thanks for the responses.
Our issue revolves around "what will be the different outcomes once we reach 1 million users?"
Will the `user` table be extremely large due to having so many extra columns for addresses, when some of the addresses could be blank? Will having 1 million users slow down a multitable query of `users` and `user_address`?
Our concern is the battle between speed and efficiency. It will take longer to query from two tables when we have a lot of records to go through, but at the same time it will take a lot of space to hold a larger table. We know we can always buy more space, but we can't always upgrade the speed. Would the speed drawback ever reach a substantial amount, or will it always be just a few microseconds slower than a single table query?
Any suggestions?
Are you going to need all one million user info at once?
If not then you can set the query to only bring up the info you are looking for.
The thing that sucks about database design is that you have two things to consider: speed, and reliability.
The higher the normal form, the more reliable and accurate your database will be - but you'll loose speed. With only a few thousand records, it doesn't matter that much. But if you're really expecting millions of records, you might be better off with a lower normal form, which will increase speed but also increase the risk of errors.
I believe that the "right" thing to do is to normalize the database as far as you can: get rid of all redundancies and repeating groups, set up all keys and relationships correctly, get rid of all partial and transitive dependencies... You won't notice the speed loss, and you'll have a correctly set-up database. Once the speed loss becomes noticeable and annoying, it's not that hard to denormalize the database to gain speed - and it's easier to denormalize a huge database to gain speed than it is to normalize a messy database to gain accuracy.
Again, thanks for the responses. Although insiteful, I'm really left at the same point I started!
I think, though, that I will recommed the switch. Not in the original version, which we are just finishing up right now, but in a v1.5 release or something. It would be easy enough right now to write a script to get the addresses and put them in a seperate table.
Since we are using PHP 5, it would be easy enough to update our classes to handle the new setup. Man... I do love OOP. :D