NYCPHP Meetup

[nycphp-talk] [OffTopic] database design question

Russ Demarest rsd at electronink.com
Tue Aug 5 09:38:47 EDT 2003


I believe the best way to normalize your tables is to apply the concept 
of wether the information is related to that ID and is not going to 
change or need to be archived. For example the users first and last 
name and username/password and date of birth, etc are only related to 
this user and there is no reason to have additional tables, Addresses 
on the other hand can change and history can be important depending on 
your project so a separate table for them is appropriate.

Your structure is good for random data that will change or be added 
later but for specific data that is unique to that ID and not going to 
change your prior table structure is the most efficient and easiest to 
code. Having over normalized (still up for debate) previous databases 
the data is nice and makes sense but writing extensive joins down the 
road become gruesome. I would recommend a blend of these two techniques.

Planning out your database with a nice entity diagram will make your 
life much easier and you should have the fields defined pretty well 
before you create your first table. Of course this is the old boring 
way but elliminates mistakes that will haunt you down the road.

Hope this helps.

On Tuesday, August 5, 2003, at 09:16 AM, jim.bishop wrote:

>
> I'm designing a new user database for a personal project, and I was 
> toying
> with a different method of storing the data than I have used before.
> Usually I create tables with logical column names that reference the 
> data
> in the column.
>
> For Example:
>
> ---------------
> | User        |
> ---------------
> | id          |
> | first_name  |
> | last_name   |
> | email       |
>
> ...
> ---------------
>
> But this becomes cumbersome when you have to add fields to the table.  
> I
> have designed a new table structure that looks like this:
>
>
> ---------------
> | User        |
> ---------------
> | uid         |
> ---------------
>
> -----------------
> | UserAttribute |
> -----------------
> | attribute_id  |
> | uid_fk        | * foreign key to user table
> | attribute     |
> | value         |
> -----------------
>
> Okay.  So I've built many user databases before and never employed the
> system below.  Has anyone else?  Besides the requisit JOIN that has to 
> be
> called with every SELECT, what is the downsides to using this kind of
> architecture?
>
>
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk
>




More information about the talk mailing list