Thanks villas.
In the example I provide (entity), specifically for web2py and
relational databases, does it actually make sense to separate 'entity'
to its own table and use 1:1 relationships from tables A/B/C, or is it
recommended to just embed the 'entity' fields into tables A/B/C
(without any references)?.
What are the pros and cons of such alternatives?.
The pro I see about using an independent 'entity' table is
maintenance, but the con might be the access / manipulation of this
extra reference ...?
Thanks again!,
Carlos
On Nov 17, 11:11 am, villas <villa...@gmail.com> wrote:
> Hi Carlos,
>
> See what you mean. We don't usually use 1:1 relationships, but 1 to
> many.
>
> e.g. From the book:
>
> db.define_table('person',
> Field('name'),
> format='%(name)s')
>
> db.define_table('dog',
> Field('name'),
> Field('owner', db.person),
> format='%(name)s')
>
> Of course an owner might only have one dog to start with, but then
> acquire others later, so this model serves for any number of dogs,
> incl. zero.
>
> The same owner might also decide to keep a cat and (assuming he needs
> to keep different data on that) he would simply define a 'cat' table
> later with a similar realtionship.
>
> I suppose if you insisted on making it 1:1, I might simply suggest
> making the dog.owner field into a unique index which would prevent
> your adding more than 1.
>
> -D
>
> On Nov 17, 3:44 pm, Carlos <carlosgali...@gmail.com> wrote:
>
>
>
>
>
>
>
> > Thanks villas and Ivan.
>
> > When I say joins I mean 1:1 relationships (or "linked tables"?), where
> > each record in tables A/B/C reference one (and only one) unique
> > 'entity' record, kind of like an 'extension' of such tables, but with
> > data centralized in the 'entity' table (which would allow better
> > maintenance).
>
> > I know I have the alternative of embedding all of the 'entity' fields
> > directly into tables A/B/C (instead of having a separate centralized
> > 'entity' table, with 1:1 relationships).
>
> > I was wondering what would be the best approach for this in the
> > relational world?.
>
> > And if a separate 'entity' table is the best option, then I guess I
> > can use the following slice to create (and update?) records with
> > linked tables:
>
> >http://www.web2pyslices.com/main/slices/take_slice/102
>
> > Thanks for your recommendations.
>
> > p.s. I have certainly already read the entire book :-)
>
> > Carlos
>
> > On Nov 17, 9:06 am, Ivan Matveev <imatvee...@gmail.com> wrote:
>
> > > >> I have a 'central' table in my design called 'entity'
> > > >> which contains lots of data (including names, company, emails, phones,
> > > >> address, etc.) and I want many other tables to point to ONE entity
> > > >> instance, i.e. 'entity' as an _extension_ of records in many different
> > > >> tables.
>
> > > When you say 'joined tables' do you mean
> > > SELECT tabl1.some_col, tabl2.other_col FROM tabl1 LEFT JOIN tabl2 ON .....
> > > or something else?
>
> > > I don't know a web2py component that can be used to edit a result of a
> > > joined select.
>
> > > If your 'entity' table includes all fields you want to present to a
> > > user you can avoid joins by use of references. I don't use db
> > > references myself. I think usage can look like this:
>
> > > db.define_table('names', Field('name', 'string'))
> > > db.define_table('companies', Field('company', 'string'))
>
> > > db.define_table('entity',
> > > Field('name', db.names.name),
> > > Field('company', db.companies.company))
>
> > > also you can alter the way a field is represented with .represent and
> > > .requires methods
>
> > > The details are in documentation on Database Abstraction
> > > Layer(DAL)http://www.web2py.com/book/default/chapter/06
>
> > > SQLFORM allows to edit a row in a table which is fine if you have all
> > > fields in 'entity' table.
>
> > > If your 'entity' table contains references to rows in other tables you
> > > will need SELECT...JOIN...
> > > To edit its result you will have to make a page with multiple
> > > SQLFORMs to edit individual rows in tables ' entity' refers to , or
> > > make a custom form with SQLFORM.factory, or invent something.
>
> > > >> My background is with object databases, and this kind of design makes
> > > >> sense, although I'm not sure if it makes sense with relation
> > > >> databases?.
>
> > > It's pretty common to store an object information in a relation db as
> > > a set of tables.
