Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2008
    Posts
    40

    new to database design, seeking consult

    Hi

    I am an architectural graduate student with nil experience designing and developing databases. I understand how they work on a schematic level though I am not up on the terminology. I have played only superficially with Access and Visio and I understand that Blog type websites have databases (controlled by PHP?) managing any posts...

    What I am looking to do s create a database that I can then feed some visualization program - think cytoscape, semaspace, tulip, prefuse etc etc - that would illustrate relationships between different entries in a network form...show how they are related, and hopefully be able to have some sort of "pivot" (MS terminology) function where the self-organizing nodes of the network would readjust as per the field being queried.

    Here's the real scenario, which also brings me to the real reason I am here : I am looking at the design of a zoo, and I am building a database of animals which will describe a number of attributes about each species. The question I have is how a REAL database designer would handle a scenario where there are potentially multiple entries for a field...like if I wanted a field for the continents you would find a particular species on - what would you do if there are multiple continents? If I were to have a drop down menu with a number of choices i would only be able to choose one. The only way I have imagined it potentially working is to have a number of continent fields, say 3, and some animals might fill those in while others might not.

    Is that how YOU would do it? Can you suggest a better way, or a better place to ask this question?

    Also, if you can think of a better workflow to get from the bits of info I am culling from the web, to inputting it into Excel and then bringing it into one of the aforementioned packages...I would appreciate hearing your thoughts.

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Don't worry about drop downs etc. Try to think about the data model and, typically, once that is complete you can build your front end once that is completed. Trying to think about your datamodel with regard to how the web page will look (especially when starting out) will trip you up again and again.

    http://www.tonymarston.co.uk/php-mys...se-design.html

    To answer your last question - no - definately not. Two columns - one is continent, the other is animal. Put in every appropriate animal-continent combination into the table.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2008
    Posts
    40
    but what if the animal lives on multiple continents?

  4. #4
    Join Date
    Apr 2008
    Posts
    29
    multiples lines per animal, one for each continent

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by m42
    multiples lines per animal, one for each continent
    Exactly.

    Read the link. Read it all. But when you get to First Normal Form, read that section twice
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2008
    Posts
    40
    ok, I get the gist of it, now how do I keep the two tables connected? I am using MS Excel.

    I realise that the page you referred to describes the principles of good database design especially w/regard to ERDs, but if you were to break it down into more practical advice how would I acheve the sort of cross referencing happening in the "First Normal Form"?

    thanks so much...

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by oompa_l
    I am using MS Excel.
    That's your mistake - you are no longer creating a database (or at least not a relational one).

    I don't know how to enforce relational integrity in Excel - I have never tried. Excel is a spreadsheet application - it is for analysis and presentation of data, not for structuring and persisting data.

    I would either give up now, drastically scale down your ambitions or use an RDBMS. Access would be perfectly adequate and you have some experience of using it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2008
    Posts
    40
    that's the kind of advice i was after - I dont have the time to become an expert in this area, this being just one step along a very complicated path. I will give the access route a go...

    if you could name just one book for someone who just wanted to make a one-time database - ddnt need to know all the ins and outs - what would it be?

    oh, and can visio take the place of access, or is it just for visualization?

    thanks again!

  9. #9
    Join Date
    Apr 2008
    Posts
    29
    Quote Originally Posted by oompa_l
    if you could name just one book for someone who just wanted to make a one-time database - ddnt need to know all the ins and outs - what would it be?
    I guess you should start with one the Access Wizards....

    Quote Originally Posted by oompa_l
    oh, and can visio take the place of access, or is it just for visualization?

    thanks again!
    Visio is for diagrams, a very different area from access... don't even bother.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You might use visio to create your logical design. Once your logical design is complete you would then base your physical design (AKA the database) on that. The logical design is substantially more difficult and time consiming than the physical design (at least for something like what you are doing).
    I would use paper if I were you though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Apr 2008
    Posts
    40
    ok, i have been trying to migrate my spreadsheet over to excel and it's clear I still am fuzzy about some of the principles.

    Back to the multiple continents issues - Say I have a table with each record corresponding to all the species being considered, and since there are cases where there needs to be multiple continents I am referring to another table with a list of the continents. It's on this table where I am lost. Is each "record" one of the contents (so 7 rows) and to the right of each continent all of the animals you might find on that continent? Or are the continents the headings for the table with the list of animals below...? In either case there will not be the same number of species in each continent which I thought might be bad design.

    this might be a sign that I am in over my head, but if I can get through this one issue I think I can apply the same approach to other attributes being considered.

    Thanks again

  12. #12
    Join Date
    Apr 2008
    Posts
    29
    Quote Originally Posted by oompa_l
    ok, i have been trying to migrate my spreadsheet over to excel and it's clear I still am fuzzy about some of the principles.

    Back to the multiple continents issues - Say I have a table with each record corresponding to all the species being considered, and since there are cases where there needs to be multiple continents I am referring to another table with a list of the continents. It's on this table where I am lost. Is each "record" one of the contents (so 7 rows) and to the right of each continent all of the animals you might find on that continent? Or are the continents the headings for the table with the list of animals below...? In either case there will not be the same number of species in each continent which I thought might be bad design.

    this might be a sign that I am in over my head, but if I can get through this one issue I think I can apply the same approach to other attributes being considered.

    Thanks again

    About the "fuzziness" i guess your first sentence says it all: This is not excel. Excel is not a database. Ditch excel and start thinking about your data...In fact, you might be better of ditching all programs by the time being and use only paper (like pootle said).

    If you have several species per continent and several continents per species, you have a many-to-many relationship, requiring three tables: The Continents table, the Species table and the SpeciesContinents table.

  13. #13
    Join Date
    Apr 2008
    Posts
    40
    ok, then can you describe the SpeciesContinent table? What would it look like, what would the columns and rows be?

    Can the primary key, or ID be the proper names? I can see numbers becoming confusing ...

  14. #14
    Join Date
    Apr 2008
    Posts
    29
    oompa_l: I guess you should google for some introductory material to Database normalization and Entity Relationship Design.

    Anyway, in the SpeciesContinents table you can have simply two columns: SpeciesID and ContinentID. Each is a foreign key to the Species and Continents tables, resp., and together they are the table's primary key ....

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Great last question - another way to phrase it is - do you use natural or surrogate keys? I would use natural keys (names). While you are learning it makes things much easier.

    The SpeciesContnent table would be two columns - one is continent, the other animal. The primary is a composite of the two (the two combined) i.e. (continent, animal).

    The rows would be like:
    Code:
    continent    animal
    --------------------------
    Europe        Rat
    Asia        Rat
    Africa        Lion
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •