[thelist] Re: [PHP-DB] Tricky Database Question

[Peter Beckman]

What about a 5th table which stores metadata:

table metadata:
    mdid    mediumint unsigned not null auto_increment
    table   varchar(15) not null
    xid     mediumint unsigned not null
    name   varchar(25) not null
    val    varchar(25) not null

The table will have all the metadata you need for any table you create.

If you want to add a "Cell Phone #" to an Owner:

   insert into metadata (table,xid,name,val) values
   ("Owner", 35, "Cell Phone #", "212-555-1212")

Now when you want to see all the metadata for a given owner, let's say you
(Sam) are Owner ID 35.   Just show all the data you are sure almost all
owners will have (home phone, address, email, etc), then do a select from
metadata where table="Owner" and xid=35.

Do that for Breed or Species and you have an unlimited but space-conscious
way to store that data.

Peter

On Fri, 18 Jul 2003, Sam Richardson wrote:

> I'm developing an application that is going to deal with information about
> different animals, and breeds of those animals.
>
> So far it is quite straight forward, I've created four tables,
>
> [Species] (containing a ID and name)
> [Breed] (also containing an ID, Name and relative link to the Species)
> [Owner] (ID, basic details about the owner such as name etc)
> [Animal] (An ID and a relative link to the owner and breed)
>
> People searching for horses/appoloosa's (species and breed) will be
> concerned with how tall they are, however someone who is searching for
> information about horses/quarterhorses will not be concerned with it's
> height but how fast it can run a quarter mile. The quarter mile may or may
> not apply to the appoloosa.
>
> Administrators using the application will have to be able to create
> attributes for the breeds on the fly, they will be based on a few various
> types of data.
>
> e.g. shorttext (for names, this will be stored as a varchar)
> longtext (for descriptions, stored as text)
> numeric (stored as an int)
> bullet lists
> check boxes
> drop lists
> etc
>
> These attributes will need to be given names and be assigned to breeds. The
> end users will fill these out when they add new horses to the database.
>
> My question is: How do I store the data from the attributes? Because each
> attribute will need to store it in a different way, do I create a table for
> each type of attribute? (Varchar, text, int etc), do I create a table for
> each individual attribute (Names, Heights etc) or do I somehow store all the
> data in one table (text for all the entries, although I may be storing short
> numbers, short descriptions, large amounts of text and abstracted radio
> buttons).
>
> The development platform is PHP with either MySQL or Postgres as needed.
>
>
> --
> Sam Richardson - Web Developer, Outwide Limited
> DDI: +64 4 381-6841 sam.richardson at outwide.co.nz
> Tel: +64 4 381-6820, Fax: +64 4 381-6821 www.outwide.com
> Level 6, 61-63 Taranaki Street, Wellington, New Zealand
> ----
> Warning: Confidential information may be contained in this message
> and any associated attachments. If you are not the intended recipient,
> you must not copy, send on or retain this message. Please delete it
> and notify the sender by reply email, thank you.
> ----
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

---------------------------------------------------------------------------
Peter Beckman                                                  Internet Guy
beckman at purplecow.com                             http://www.purplecow.com/
---------------------------------------------------------------------------