Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Those two "records" are significantly different. For example some contacts have multiple phone numbers and others have only one. Additionally, the number of contacts may vary. It seems to lend itself to a relational database schema with multiple tables. However, you're going to have a hard time automating the process of populating multiple relational tables based on the data layout. How much data are you dealing with? If it's not an enormous amount, you may be better off doing this sort of half-manually, reformatting parts of your Textpad doc into INSERT statements (using lots of regular expression search and replaces), and taking some time running the queries. </p> <p>If it's a truly large amount of data, then you might want to write a little program in the language of your choice to parse the file and create an output file containing the appropriate insert statements to populate all the data tables.</p> <p>For a robust relational database, you'd want a database schema that includes at a minimum, the following tables:</p> <pre><code>Categories (map category names to IDs) Types (map Group Types to IDs) Organizations (including category and group type ID, name, addr, objs, activities, meetings, website, email, and an organization ID) Contacts (one to many referencing org ID, including contact name and a contact ID) Phone (one to many referencing contact ID, including phone type and phone number) </code></pre> <p>You could get away without the Types and Categories tables, but they may prove useful depending on the volume of data and how they plan to query on it in the future (e.g. if at some point they will want to find all organizations in a particular category of a particular group type, and there are at least thousands of organizations, then the extra tables will prove worthwhile).</p> <p>Since the contact/phone information appears to be so flexible, you're better off putting it into separate tables -- otherwise you'd have to include columns in the main organization table for contactN/phoneN/phoneTypeN for the maximum number of possible contact/phones, and that would also create a limit on how many contact/phone associations could be made.</p> <p>You had also better make sure that none of the records require multiple entries for any of the other fields (MEETINGS, EMAIL, ...). If that is a possibility, then you again need to make a choice of whether to add additional relational tables, or add multiple fields for the max possible to the organization table.</p>
    singulars
    1. This table or related slice is empty.
    1. POConvert Raw Partially Structured Text into CSV or Tab Delimited Document or XML
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USJeremy Goodell
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POConvert Raw Partially Structured Text into CSV or Tab Delimited Document or XML
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COJeremy, thanks for the help and the effort but I am past this. So your first observation is the problem. I am down in the trenches preparing the raw data. I may have to edit my question. The ERD is complete and reviewed by my mentor. It is flexible enough to deal with data. The data needs to be mapped to corresponding field in the applicable table.
      singulars
      1. PO
      1. USdiek
    2. COYou could use my advice about writing a simple parsing program in the language of your choice that would read the input file (as you specified) and then generate a file with a series of INSERT statements corresponding to the parse. I would estimate that it would take me about an hour to write a Java program to do this.
      singulars
      1. PO
      1. USJeremy Goodell
    3. COJeremy, that does have me thinking. And that would be something I would love to code! So I would obviously have to go through the text file and ensure all the subject headers matched? I am pretty certain that will be a 'yes', but I cannot imagine I am the first person to have this issue and someone has not solved it/ or made it easier already. Thanks for sticking around.
      singulars
      1. PO
      1. USdiek
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload