[OT] database design help

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[OT] database design help

Marc Lucke
Hi,

I'd like some advise about proper db design.

Let's say I want to ask color lollies people like.  The answer might be
red, green, blue & turquoise.  I have 2 problems that I'm scratching my
head about - I don't know (nor do I want to anticipate) every color that
might be given, and I don't know the best way to represent this using
each table.

I'm tempted to do this:

name: marc
lolly_colors: red,green,blue,turquoise

- i.e. to use a comma delimiter

Is that the way to go, or does good db design prescribe a better
method?  If so, what is this method specifically called?  Can you point
me to further reading.

As ever, help is very appreciated.


Marc

============================================
Attend the Lasso Summit
March 2-7, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT] database design help

Michael Coninx
Hello Marc,

You have a "one to many" relationship :
1 person  can have one or more favorite lolly colors.

So you should use two tables:

"Person" and "Lolly Color"

This way you can retrieve all colors a person likes, but also all
persons who like color "red" eg.
You only have to make sure all equal colors are stored in the same way,
by name or by an unique ID.

Hope this helps,

With kind regards,

Michael

Marc Lucke wrote:

> Hi,
>
> I'd like some advise about proper db design.
>
> Let's say I want to ask color lollies people like.  The answer might
> be red, green, blue & turquoise.  I have 2 problems that I'm
> scratching my head about - I don't know (nor do I want to anticipate)
> every color that might be given, and I don't know the best way to
> represent this using each table.
>
> I'm tempted to do this:
>
> name: marc
> lolly_colors: red,green,blue,turquoise
>
> - i.e. to use a comma delimiter
>
> Is that the way to go, or does good db design prescribe a better
> method?  If so, what is this method specifically called?  Can you
> point me to further reading.
>
> As ever, help is very appreciated.
>
> Marc
>
> ============================================
> Attend the Lasso Summit
> March 2-7, 2007 in Fort Lauderdale, FL
> http://www.LassoSummit.com/
> ============================================
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:  
> http://www.listsearch.com/lassotalk.lasso?manage
>


============================================
Attend the Lasso Summit
March 2-7, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT] database design help

Marc Vos-2
In reply to this post by Marc Lucke
A quick google "data modeling" came up with some stuff of which this  
link explains it quite simple:

        http://www.agiledata.org/essays/dataModeling101.html

Regards,
-Marc Vos

* * * * * * * * * * * * * * * * * * * * * * * *
Goodyear Dunlop Tires Germany GmbH
Private:  http://marc.vos.net/
iChat/AIM : mhevos
* * * * * * * * * * * * * * * * * * * * * * * *



On 05-dec-2006, at 12:37, Marc Lucke wrote:

> Hi,
>
> I'd like some advise about proper db design.
>
> Let's say I want to ask color lollies people like.  The answer  
> might be red, green, blue & turquoise.  I have 2 problems that I'm  
> scratching my head about - I don't know (nor do I want to  
> anticipate) every color that might be given, and I don't know the  
> best way to represent this using each table.
>
> I'm tempted to do this:
>
> name: marc
> lolly_colors: red,green,blue,turquoise
>
> - i.e. to use a comma delimiter
>
> Is that the way to go, or does good db design prescribe a better  
> method?  If so, what is this method specifically called?  Can you  
> point me to further reading.
>
> As ever, help is very appreciated.
>
>
> Marc
>
> ============================================
> Attend the Lasso Summit
> March 2-7, 2007 in Fort Lauderdale, FL
> http://www.LassoSummit.com/
> ============================================
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:  http://www.listsearch.com/ 
> lassotalk.lasso?manage


============================================
Attend the Lasso Summit
March 2-7, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT] database design help

David Singleton-4
In reply to this post by Marc Lucke
I'm guessing that you've simplified this down a bit more than it actually
is so I'll try and respond to something that could be a bit more complex.
The answer no, thats not the "correct" way to do this. I say "correct"
because in actuallity, there is no correct way, just a way database
creation standardization isn't something that is around right now.  There
is no "standard" way to name a DB, Table, or field, its all up to the user.
In your situation, there are two options you could go with that I would
consider "good" database design.

1.  Table that has one record for each person, and a bunch of Y or N
fields.  For instance:
Name  Red   Green Blue  Yellow      Turquoise   Brown
Marc  Y     Y     Y     N     Y           N

This type of design has been used by numerous people before and it works
great.  However, the problem that I have with this design is that what
happens when you want to add another choice, say, Purple.  Its not horribly
difficult, however, you would have to create the field, then set all blank
purples to N.

2.  Table that has one record for each type of lollie that the person
likes.
ID    Name  LollieColor PersonID
1     Marc  Red         1
2     Marc  Green       1
3     Marc  Blue        1
4     David Red         2
5     David Blue        2

Then of course you would want a People table that related to the PersonID,
so that you can keep track of any info on the person that you want.  The
issue with this one is that you have to go through multiple records to get
all of the Lollies that one person likes.  However, adding a new color to
the LollieColor is as simple as giving the user that choice.

Those are my two suggestions, but its really just a matter of choice and
which situation best fits your needs.


Thank You,
David Singleton
Walsworth Publishing Company
Product Development
(660)258-2104 Ext. 4314


                                                                           
             Marc Lucke                                                    
             <[hidden email].                                            
             au>                                                        To
             Sent by:                  <[hidden email]>        
             <[hidden email]                                          cc
             world.com>                                                    
                                                                   Subject
                                       [OT] database design help          
             12/05/2006 05:37                                              
             AM                                                            
                                                                           
                                                                           
             Please respond to                                            
             <[hidden email]                                            
                world.com>                                                
                                                                           
                                                                           




Hi,

I'd like some advise about proper db design.

Let's say I want to ask color lollies people like.  The answer might be
red, green, blue & turquoise.  I have 2 problems that I'm scratching my
head about - I don't know (nor do I want to anticipate) every color that
might be given, and I don't know the best way to represent this using
each table.

I'm tempted to do this:

name: marc
lolly_colors: red,green,blue,turquoise

- i.e. to use a comma delimiter

Is that the way to go, or does good db design prescribe a better
method?  If so, what is this method specifically called?  Can you point
me to further reading.

As ever, help is very appreciated.


Marc

============================================
Attend the Lasso Summit
March 2-7, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:
http://www.listsearch.com/lassotalk.lasso?manage



============================================
Attend the Lasso Summit
March 2-7, 2007 in Fort Lauderdale, FL
http://www.LassoSummit.com/
============================================

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage