Ok.. that wasn't really ideal, a better (and more normalized) solution might look a bit like this:
Sample Table: -> sample_id (pk) -> sample_type_id (fk) -> other descriptive fields Sample Type Table: -> sample_type_id (pk) -> other descriptive fields Attribute Type Table: -> attribute_type_id (pk) -> attribute_name -> attribute_datatype Attribute Tables:* ->attribute_id (pk) -> sample_id (fk) -> attribute_value *one attribute table for each appropriate datatype, ie string attribute table, integer attribute table, float attribute table, etc. With the above schema you should be able to allow users to define sample types, store those definitions in the database, and programattically create the necessary forms for any sample type necessary. On Sat, Jun 19, 2010 at 3:34 AM, <srb...@gmail.com> wrote: > I'm thinking you should be able to do this without having users create > tables. Three separate tables should be enough. Maybe more if you want to > get fancy. > > Sample table: holds sample id & sample type (fk) > > Sample type table: holds sample type id & comma separated list which > defines number and type of attributes for that type of sample. > > Attribute table: holds attribute name, attribute value, and foreign key to > a sample id. > > The sample type table is only needed to generate a form for new samples. > The attribute table could be broken up by data type if necessary as well. > > Sent from my Verizon Wireless BlackBerry > > -----Original Message----- > From: llanitedave <llanited...@veawb.coop> > Date: Fri, 18 Jun 2010 23:27:55 > To: Django users<django-users@googlegroups.com> > Subject: Re: Database Design Question > > Thanks for the response, Venkatraman. You're right that I don't > anticipate a huge number of records here -- a few hundred thousand at > the extreme high end. Sharding isn't something I considered, and I > don't think it would be necessary. > > I guess it's mostly a normalization question. > > And while I was typing out a long explanatory discussion to enlarge on > the problem, I stumbled across the answer. > > I'll need to use a separate table for each sample type to store its > unique set of attributes. > > For example, a fluid sample type might have the fields: volume, > concentration, pH, and expiration date. > A soil sample might have the fields: Clay content, color, grain size, > moisture content. > > All sample types will have the common fields sample ID, sample type, > date collected, collector, source, and storage location. > > So I'll need a generic "Sample" table and then related tables for each > sample type. > > The difficulty is that many sample types are still undefined, and will > have to be defined by the individual users. That means they'll have > to have an interface that allows them to create tables and fields in > the database. I suppose I can put together a form for that which will > ensure it gets done in a limited, consistent and standard way. > > I'm thinking out loud here, but it's getting the feedback that helped > to clarify it. Any other comments or suggestions will be welcome. > > On Jun 18, 9:46 pm, Venkatraman S <venka...@gmail.com> wrote: > > On Sat, Jun 19, 2010 at 10:12 AM, Venkatraman S <venka...@gmail.com> > wrote: > > > Prefer a table like follows (tblname:samples): sampleid, samplename , > > > sampledesc etc etc > > > > Ok - i missed explaining why i would recommend this: > > > > In most of the applications, maintainence is a bigger pain than > development. > > > > In your case, i do not think that this table would contain a billion > records > > and even if it does, this design helps in sharding. Maintaining this > system > > in latter case would be more of a db-admin or sys-admin job - and already > > there are many solutions in addition to sharding, when the size of a > single > > table is HUGE. (when Facebook can contain the entire 32Gb profile data in > > the RAM, doesnt the world look small? ;) ) > > > > -V-http://twitter.com/venkasub > > -- > You received this message because you are subscribed to the Google Groups > "Django users" group. > To post to this group, send email to django-us...@googlegroups.com. > To unsubscribe from this group, send email to > django-users+unsubscr...@googlegroups.com<django-users%2bunsubscr...@googlegroups.com> > . > For more options, visit this group at > http://groups.google.com/group/django-users?hl=en. > > -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-us...@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
