Re: [libreoffice-users] Can I use a listbox to query database
Hi :) The simple answer is Yes for most of it but for the last question there it's No (because you are not asking too much). The detailed answer from Michael kinda assumes you are somewhat familiar with spreadsheets. Spreadsheets are flat and many fields/cells contain identical data. For example if one of your most prolific presenters is called Firdausi then you would have a lot of repetitions of her name. Any mis-spellings or typos would result in one or more lines missing when you searched for the presentations she could do. A database can be relational rather than flat. You would have 2 or more tables. Each row would have a unique identifier, called an ID which is normally generated automatically. One of your tables, or a separate one would link the tables together. Lets say you had 1 table called Names and 1 called Topics (ie keeping names short and simple to help humans do redesigning in the future). Lets assume you have additional tables such as Address and Venue but lets kinda just ignore those. Now the topics table might have headings such as; ID= an automatically generated and reasonably small whole number (you are unlikely to need 12 digits in here!) Title= text-field, probably under 256 characters Description = text-field, avoid setting limits here in order to avoid problems in the future. If users disobey the 3 keywords rule you kinda need the system to be fail-safe. Person = Name.ID = small whole number but this value is set by the list-box rather than being generated automatically The Names table might have headings/fields such as; ID = auto-generated small(ish) whole number again Name1st = text-field under 256 characters NameLast = text-field under 256 characters Address = Address.ID Now the relationship between those 2 tables is that the ID field in the Names table is a 1-to-1 relationship with the Person field in the Topics table. However between the Names and the Address tables you would probably have a many to one relationship because you might have 2 or 3 different people all at the same address. Actually my brain is a bit fried and it might also be a 1-to-1 because it looks like we might have neatly dodged that extra complication. Anyway, the point is that you can have different tables, with different relationships between them. The normal users see none of that. It's only the database designers who get that level of detail (unless you are using MS Access in which case it's tricky to avoid normal users stumbling into designers turf and accidentally make a horrible mess and even break things without having a clue what they did and thus being completely useless at helping give enough information to help fix the problem). With Base normal users could just be seeing familiar Writer documents. These are really Forms or Reports. Normal users can change the formatting and wording and shift things around. The documents might have drop-downs to help them move between different topics or presenters, or venues or addresses. You set what normal users have access to. Really clever ones might figure out how to access more fields, in which case 'promote' those rare individuals to help design new documents for other things Data-entry people would need to go into Base and probably use something that looks a lot like a Calc spreadsheet (but with drop-downs). They might tumble into designer turf but are likely to be smart enough to know to go get help or to be able to get back into their own area and rescue themselves with a click or 2. Designers seldom need to see the data itself but just work with the headings, tables layouts, relationships and maybe help set-up forms and reports for normal users in Writer. So, with Base (unlike MS Access) each level of people involved can get on with just the area they are good at. They don't need to get involved in the complexities of anyone else's role, for the most part. Also you can see that while flat spreadsheets can get very large very quickly and has a high potential for breakages and problems the typical database stays much smaller and is thus usually a lot faster and smoother. Regards from Tom :) On 12 December 2013 06:22, Michael Manning michaelgmann...@gmail.com wrote: Bob, Can I make the suggestion that you use 3 tables - one for the presenters, one for the presentations, and one for the topics. You may need another one but we will get to that in a minute. I am suggesting a separate table for presenters as one presenter may make a number of presentations over time and so you will not have to repeat data in the tables - one of the rules of data normalisation. Each presentation will also link to a number of topics. It is possible to setup the tables so that each presentation can link to any number of topics. The structure outlined above will waste database space if there are fewer than 3 topics assigned and will have a problem if you wish to assign
Re: [libreoffice-users] Can I use a listbox to query database
Hi :) Ooops! I meant to stop at the end of the 1st paragraph there! It seems Mike and i disagree on a few points. 1. I try to get names of things as different as possible for the sake of clarity. At 3am after 12 cups of coffee the eye can sometimes skate over the difference between presentations and presentor especially if you have to adjust the size of columns each time to see the last few letters. 2. The use of the word topics. I was thinking of each presentation being only about 1 topic. Then i stumbled into the problem of not having a name for what the 3 keywords are for. Michael uses the word more correctly imo. 3. I tend to group similar thing togather, so use fields such as Name1st (well, i use Nm1st) and NameLst. However if the table is called Name then headings such as 1st and Last would be seen in Forms, Reports and Queries as Name.1st, Name.Lst anyway 4. I got muddled about the different relationships and i'm fairly sure i was wrong there 5. In the examples i gave i just kept the ID heading short but Mike's way is better even if i think those headings are t long and too similar. It's better to have a little clue as to which tables these similar things belong to. In my defence i'd say that it doesn't really matter but again the 12 cups of coffee issue comes into play. Clarity helps. Actually none of these or similar disagreements really matters, except point 4. It just shows there are slightly different ways of doing things or ways of saying things but both of us had much the same approach. Mike went for using an additional table to pull the relationships together whereas i took a short-cut and used an existing table. Mike's way is better. Regards from Tom :) On 12 December 2013 12:01, Tom Davies tomc...@gmail.com wrote: Hi :) The simple answer is Yes for most of it but for the last question there it's No (because you are not asking too much). The detailed answer from Michael kinda assumes you are somewhat familiar with spreadsheets. Spreadsheets are flat and many fields/cells contain identical data. For example if one of your most prolific presenters is called Firdausi then you would have a lot of repetitions of her name. Any mis-spellings or typos would result in one or more lines missing when you searched for the presentations she could do. A database can be relational rather than flat. You would have 2 or more tables. Each row would have a unique identifier, called an ID which is normally generated automatically. One of your tables, or a separate one would link the tables together. Lets say you had 1 table called Names and 1 called Topics (ie keeping names short and simple to help humans do redesigning in the future). Lets assume you have additional tables such as Address and Venue but lets kinda just ignore those. Now the topics table might have headings such as; ID= an automatically generated and reasonably small whole number (you are unlikely to need 12 digits in here!) Title= text-field, probably under 256 characters Description = text-field, avoid setting limits here in order to avoid problems in the future. If users disobey the 3 keywords rule you kinda need the system to be fail-safe. Person = Name.ID = small whole number but this value is set by the list-box rather than being generated automatically The Names table might have headings/fields such as; ID = auto-generated small(ish) whole number again Name1st = text-field under 256 characters NameLast = text-field under 256 characters Address = Address.ID Now the relationship between those 2 tables is that the ID field in the Names table is a 1-to-1 relationship with the Person field in the Topics table. However between the Names and the Address tables you would probably have a many to one relationship because you might have 2 or 3 different people all at the same address. Actually my brain is a bit fried and it might also be a 1-to-1 because it looks like we might have neatly dodged that extra complication. Anyway, the point is that you can have different tables, with different relationships between them. The normal users see none of that. It's only the database designers who get that level of detail (unless you are using MS Access in which case it's tricky to avoid normal users stumbling into designers turf and accidentally make a horrible mess and even break things without having a clue what they did and thus being completely useless at helping give enough information to help fix the problem). With Base normal users could just be seeing familiar Writer documents. These are really Forms or Reports. Normal users can change the formatting and wording and shift things around. The documents might have drop-downs to help them move between different topics or presenters, or venues or addresses. You set what normal users have access to. Really clever ones might figure out how to
[libreoffice-users] Can I use a listbox to query database
I am new to databases in general and LibreOffice Base in particular. I am using LO Version: 4.1.2.3 installed into Windows 7 Home Premium Service Pack 1 I want to create a database with information for about 100 presentations. It would contain the name of the presenter, the title, and three “keywords” or topics. There are two tables consisting of a main table (that contains the PresenterID, PresenterName, Date, Title, Topic1, Topic2, and Topic3) and the table of topics for the listboxes. I have figured out how to create a single form to fill in the data for the two tables that uses 3 listboxes to present the choices for the topics. I want to make the database easily searchable for people with little computer experience. Is there a way to present a screen to such users with dropdown lists of the topics (same entries as in the Topics table) that will produce a list of the relevant presentations? I don’t what them to have to deal with creating a query or a report. Am I asking for too much? Thanks Bob -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Can I use a listbox to query database
Bob, Can I make the suggestion that you use 3 tables - one for the presenters, one for the presentations, and one for the topics. You may need another one but we will get to that in a minute. I am suggesting a separate table for presenters as one presenter may make a number of presentations over time and so you will not have to repeat data in the tables - one of the rules of data normalisation. Each presentation will also link to a number of topics. It is possible to setup the tables so that each presentation can link to any number of topics. The structure outlined above will waste database space if there are fewer than 3 topics assigned and will have a problem if you wish to assign more than 3 topics. The presenters table would contain: presenterID - primary key firstname lastname any other field you wish to add regarding the presenter The presentations table would contain: presentationID - primary key date of presentation title of presentation presenterID - foreign key into the presenters table other fields relating to the presentation This sets up a one - many relationship between presenters and presentations ie one presenter can be linked to many presentations With the topics table, a suitable structure may be: topicID - primary key topic_name other fields relating to the topic The relationship between presentations and topics is not a simple one-to-many relationship. As you have described, a single presentation may be associated with many topics, and a single topic may be associated with many presentations. This is a many-to-many relationship and is best expressed via an intermediate table - presentation_topic The presentation_topic table would have the following structure presentationID topicID The two fields together form the primary key for this table. The relationship structure for the database is then as follows: The presenter table links to the presentation table via the presenterID field and the presentation table links to the topics table via the presentationID+topicID fields in the intermediate table. This structure will allow you to query the database and ask the following questions Who presented on a particular presentation How many presentations has a particular presenter made, when were these made, and what were the topics What presentations were made in a particular date range What topics were allocated to a particular presentation What presentations have been made concerning a particular topic, or number of topics. Once that data structure is in place, add some test data to see that the structure is working and you are able to retrieve the required data. Once this is successful, it is time to prepare some forms and reports so that users of the database can easily use the system. This would include drop-down lists in the forms. Mike On Thu, Dec 12, 2013 at 2:01 PM, Bob Muir rmu...@triad.rr.com wrote: I am new to databases in general and LibreOffice Base in particular. I am using LO Version: 4.1.2.3 installed into Windows 7 Home Premium Service Pack 1 I want to create a database with information for about 100 presentations. It would contain the name of the presenter, the title, and three “keywords” or topics. There are two tables consisting of a main table (that contains the PresenterID, PresenterName, Date, Title, Topic1, Topic2, and Topic3) and the table of topics for the listboxes. I have figured out how to create a single form to fill in the data for the two tables that uses 3 listboxes to present the choices for the topics. I want to make the database easily searchable for people with little computer experience. Is there a way to present a screen to such users with dropdown lists of the topics (same entries as in the Topics table) that will produce a list of the relevant presentations? I don’t what them to have to deal with creating a query or a report. Am I asking for too much? Thanks Bob -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted