Hey John,
Welcome to the community.
I'm not sure I understand your requirements exactly, but here is my
thoughts on a schema:
For the initial MVP, I usually like to keep the amount of tables to a
minimum, then as I figure out the common queries I can separate them out
into multiple tables.
For an app such as yours, I would use validators and autocomplete, to
ensure State, County, City/Town are the same:
Also, if ALL the fields are the same, and the only difference is the record
type, why not add that as a different field?
IE:
VALID_RECORD_TYPES = ['Birth Certificate','Death Certificate']
db.define_table('genealogical_records',
Field('location_state','string'),
Field('location_county','string'),
Field('location_city_town','string'),
Field('record_type','string',requires=IS_IN_SET(VALID_RECORD_TYPES)),
Field('attribute_1','type')).....
As the database grows, you could change VALID_RECORD_TYPES to receive a
function that is a select if you want it more dynamic:
db.define_table('valid_record_types',
Field('label','string'),
Field('input_attributes','list_string'))
def get_valid_record_types():
return [(row.id,row.label) for row in db(db.valid_record_types).select
()]
VALID_RECORD_TYPES = get_valid_record_types()
Also, you'll notice I put in the input attributes:
I find a common design pattern I use is:
fields = []
fields.append(db.table.field)
form =SQLFORM(*fields)
With this pattern in place, you could forseeably store the data fields you
want to create for the form in the table for each record type
or you can do this in a module and import a list of fields you want to edit
for each record type.
You can use groupby to get records grouped by city, state, etc.
Sorry if I'm chiming in and dont understand the requirements fully.
Obviously in the first part, you could obviously make those location fields
references to a database table, but then you have a more complex join
operation for every update and select.
-Mark
On Saturday, April 25, 2015 at 10:28:10 AM UTC-5, John wrote:
>
> Gramps is a desktop program for building a family tree. We have a web
> based version that we're fine with although I'd like to tie it into the
> actual site sometime for joint login purposes. This other part of the site
> is the individual transcriptions of old records. The data you would
> reference/cite as sources for facts in doing research for building a family
> tree.
> Upon further thought, places (State, County, CityTown) be best in it's own
> table so they each only get stored once.
>
> On Tuesday, April 21, 2015 at 10:05:32 AM UTC-5, John wrote:
>>
>> New to programming and web2py. Been dabbling in web design for a while,
>> building sites with wordpress, plugging things in. My first real project
>> will be a site/app for my sister. Genealogical resources. Lots of records
>> that will need to be added via csv and also via forms. Will also need to be
>> displayed in sortable grid/table format and will need advanced search
>> capabilities. These records will be things like births, deaths, marriages
>> etc and would have anywhere from 10-20 fields for things like; surname |
>> given name(s) | DOB | State | County | City/Town | Father surname | father
>> given name | mother surname | mother given name | source.
>> On the site, people would most likely browse by clicking a State, then
>> the County and then Birth records at which point they would see the
>> sortable grid/table with all birth records for that County, State.
>> Else they would go to an advanced search page where they would choose the
>> record type from a drop down, State from a drop down which would then
>> populate a drop down for Counties of the chosen State. A choice for all
>> States/Counties would be nice. Names, they would type in. DOB could be
>> typed in as well though it would be nice if they could choose a date or
>> date range. Some records may only have a partial date like maybe just the
>> year.
>> Each record type will have all the same fields of course so it only makes
>> sense to have a table for each. There will be approx 20-30 different record
>> types. I thought about individual tables for each record type for each
>> state but that would be 51 tables(50 States plus DC) times however many
>> record types there ends up being so 1000-1500 tables. Seems like too many.
>> I'm in no rush and realize I have a lot of work to do and a lot to learn.
>> At some point this site will hopefully become a community based site so
>> membership and permissions will be needed. A forum would be nice but I
>> haven't seen anything regarding that in the documentation yet. But first
>> and foremost is the data records. I've read that each application gets it's
>> own database so I'm thinking my first/main app would be *Records* with
>> db tables for births, deaths, marriages etc. Am I correct in thinking that
>> a table for each record type would be best and to not break it down by
>> State but rather make State a field/column? Then again, when/if we end up
>> with lots of records, that would be a lot of rows considering the number of
>> births in the USA over a period of a few centuries.
>>
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
