table preference

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bhonan
Forum Newbie
Posts: 14
Joined: Sat Feb 02, 2008 12:55 pm

table preference

Post by bhonan »

What is your preference for tables in a database??

Lots of little tables? ...with a bunch of connections, like users only contains (id, username, password) and contacts coontains (id, firstname, last name, userid) and them maybe another one for address connected to the contact id... etc...

or

Few big tables? ...few tables that store all the info on a specific person... one table for id, username, password, firstname, lastname, address.... etc....
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: table preference

Post by Christopher »

I would recommend starting with smaller normalized tables. However, if contacts and users are 1:1 and you will never access the contacts table alone -- then that data can be put in users.
(#10850)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: table preference

Post by califdon »

bhonan wrote:What is your preference for tables in a database??

Lots of little tables? ...with a bunch of connections, like users only contains (id, username, password) and contacts coontains (id, firstname, last name, userid) and them maybe another one for address connected to the contact id... etc...

or

Few big tables? ...few tables that store all the info on a specific person... one table for id, username, password, firstname, lastname, address.... etc....
To me, there is no such thing as "preferences" about structuring a database schema. There is only the right way and all the others. Relational database theory specifies how decisions should be made for tables and the columns in each one. There are lots of references to this online, I'll list a few at the end of this. But basically, a database is a model of some segment of the real world. You have to decide what the boundaries are for your model, identify the entities that you will represent in your model, and each entity becomes a table. It has nothing to do with size or numbers. It has to do with mathematical set theory. I suggest that you read these and more:
http://www.databasejournal.com/sqletc/a ... hp/1469521
http://www.campus.ncl.ac.uk/databases/d ... esign.html
http://www.deeptraining.com/litwin/dbde ... esign.aspx
Post Reply