Hi Carlos
In the relational model, you would separate it. The idea is that you
group your data into logical datasets (tables), then you join it all
together by using foreign keys. The idea is that data only ever has to
be entered once and there is little wasted space.
These days we also have the new non-sql DBs which have a different
logic. With that model you try your best to put as much data as
possible into large tables. It's also OK to have redundancy of data
provided the queries are faster. Under that system, we exchange the
elegance of the relational model for faster query speed. If you
really do think that 1:1 is the way to go, you can consider having
one big table containing all the fields you'll ever want. OK,
there's some wasted space, but modern computers don't usually have a
problem and you can run it on Google App Engine too.
It's horses for courses, but generally speaking, enterprise software
is ausually based on the relational model. Big global websites where
speed is paramount are based on non-sql.
-D
On Nov 17, 6:48 pm, Carlos <carlosgali...@gmail.com> wrote:
> 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.
>
>
