NYCPHP Meetup

[nycphp-talk] [OffTopic] database design question

jim.bishop nyphp at jimbishop.org
Tue Aug 5 09:47:58 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.


i had been thinking of that as well.  i think you're right about the JOIN
nightmare to follow.  i just wanted someone else to tell me that it would
be a nightmare, so i wasn't not trying it because it seemed too abastract.

thanks!



>
> 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
> >
>
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk
>




 -------------------------------------------------------
 -------------------------------------------------------
 -> the syntax of cummings, the absurdity of faulkner <-
 || jimbishop.org  ||   jim.bishop   ||  i heart n y  ||
 -------------------------------------------------------
 -------------------------------------------------------



More information about the talk mailing list