Now that's a fascinating idea, because the three-table solution is very similar to something I tried in the 1990's using a tool called 4th Dimension. I've been out of database development for about a decade, and this is my first foray into Django since doing the tutorials. The previous database had only a few sample types, and the existing design limited our ability to expand. The idea that you've given here is pretty much the same one I came up with when trying to find a way to expand its capabilities. I never could get the interface quite the way I wanted it using 4D, and the idea was never implemented, but Django looks like it can give me a bit more flexibility. I hadn't thought of using a separate table for each attribute data type, though. That might be something to give a whirl to.
Thanks, all! On Jun 19, 1:52 am, S Basl <srb...@gmail.com> wrote: > 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.
