• Post Reply Bookmark Topic Watch Topic
  • New Topic

Directory-based people database  RSS feed

 
Ranch Hand
Posts: 117
Chrome Mac Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm working on a database, for now it will be directory based as I need my program to be functional within a month or so. I will later design a database schema and work from there, but I have some questions.

The Person class itself will look as such:

Person
--------
-personID <int>
-firstName <String>
-middleName <String>
-lastName <String>
-phoneNumber <String>
-emailAddress <String>
---------
setFieldName()
getFieldName() (for all fields except studentID.
---------


Now the directory structure should be as such:

p1
p2
p3
...
pN

Inside each directory there will be a person.xml file with the relevant data for that particular ID.

What I'm having difficulty with is how to keep track of the IDs. When a new person is added, I'd like for it to just auto-increment, but the solutions I've thought of so far aren't very elegant IMHO:

1) Just save the last ID number in a file and then read from the file when the program loads
2) Serialize a class with the value

I really don't like solution #1, and I don't know what I would name the class. I've also just thought that I could serialize the person objects in each directory and perhaps make personID static.

Any better suggestions?



 
Ranch Hand
Posts: 98
Eclipse IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In my opinion you are probably worth getting the database up and running now, as this might be a case of trying to save time now will cost you far far more time in the future. The database is probably a more elegant solution, however without knowing more of the particular scenario it is hard to judge just how complex the schema would be.

S
 
Bartender
Posts: 10575
66
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Stuie Clarky wrote:In my opinion you are probably worth getting the database up and running now...

And I second that advice.

However, if your only worry is how to create a unique id, there are plenty of possibilities; just one of which is to use Java's UID or UUID classes. Or you could create your own using some sort of combo of System.currentTimeMillis() + cyclic or random sequence (or both) that fits in a long.

I still think Stuie's advice is best though, regardless.

Winston
 
Paul Mrozik
Ranch Hand
Posts: 117
Chrome Mac Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your input Stule and Winston, I'm working on the database schema now. The schema itself shouldn't be too complex, and I'll take a look at UUID as well.

 
Bartender
Posts: 6109
6
Android IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Winston Gutkowski wrote:System.currentTimeMillis() + cyclic or random sequence (or both) that fits in a long.


Or just use a monotonically increasing long. You'd have to use a file (or java.util.Preferences) to store the value between runs of your app.
 
Paul Mrozik
Ranch Hand
Posts: 117
Chrome Mac Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okay, so here's a screenshot of the most important part of my program:

Lesson Review Screenshot

and the schema:

Lesson Review Schema

I'll figure out how to add and remove students, that probably won't be a problem.

What I am having difficulty with is the word table. As you can see from the diagram, there's a many-to-many relationship. I could have just made it a one-to-many relationship since it would make things easier, but then there'd be a lot of redundancy. A lot of students could have the word 'redundant' and it would be in the database five times.

So I thought that it would be much better to:

1. Have a table with all the words used so far
2. Associate the word used with reviews

The process would be:

1. Check whether the word already exists in the database (preferably run a spell check as well)
2. If the the word doesn't exist, add it to the database and create a new entry in the linking table
3. If the word exists, create a new entry in the linking table to link it with the review

I'm assuming this should be done in the program itself and not via database constraints or scripts?

Suggestions are, as always, greatly appreciated.









 
Ranch Hand
Posts: 335
6
Linux Tomcat Server Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1. Check whether the word already exists in the database (preferably run a spell check as well)
2. If the the word doesn't exist, add it to the database and create a new entry in the linking table
3. If the word exists, create a new entry in the linking table to link it with the review


To me it seems this process would be better is a database stored procedure / function. If your process is done from the program then
1. has to do a select, if there is no return then
2. has to do an insert , then another select has to be done to get the new id from the table
3. then does another insert into the linking table with the new word id
so you are calling the server 4 times from the program

I admit that I like databases and try to store logic in them so there is consistency when the database is updated from multiple applications. I work in a place where multiple IT departments use different programming languages to access company data. I have seen the chaos that results when the business rules are in the programs, isolated from the data.

A question, how are you handling plurals in your word table? If I were searching for "word" I would hope that "words" would be a related search, and what about plurals that have a spelling change ? elf = elves , tooth = teeth
 
Paul Mrozik
Ranch Hand
Posts: 117
Chrome Mac Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
margaret gillon wrote:
1. Check whether the word already exists in the database (preferably run a spell check as well)
2. If the the word doesn't exist, add it to the database and create a new entry in the linking table
3. If the word exists, create a new entry in the linking table to link it with the review


To me it seems this process would be better is a database stored procedure / function. If your process is done from the program then
1. has to do a select, if there is no return then
2. has to do an insert , then another select has to be done to get the new id from the table
3. then does another insert into the linking table with the new word id
so you are calling the server 4 times from the program

I admit that I like databases and try to store logic in them so there is consistency when the database is updated from multiple applications. I work in a place where multiple IT departments use different programming languages to access company data. I have seen the chaos that results when the business rules are in the programs, isolated from the data.

A question, how are you handling plurals in your word table? If I were searching for "word" I would hope that "words" would be a related search, and what about plurals that have a spelling change ? elf = elves , tooth = teeth


I think you're right, it probably would be better if stored as a database procedure. I will have to learn the syntax for MySQL procedures then

A good point on the plurals - I hadn't even considered it. With most words I suppose that it wouldn't be a problem because we can just add s or es, but with the irregular words I see no other way than to just populate the database with the irregular plurals.

 
Marshal
Posts: 56600
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Mrozik wrote: . . .

A good point on the plurals - I hadn't even considered it. . . . but with the irregular words I see no other way than to just populate the database with the irregular plurals.

What is wrong with populating the database with all plurals. It might be more work initially, but the programming afterwards may be easier. I have had such a database since I was about three years old, including the rule about adding ‑s. That database is installed somewhere in my head.
 
Winston Gutkowski
Bartender
Posts: 10575
66
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeff Verdegan wrote:Or just use a monotonically increasing long. You'd have to use a file (or java.util.Preferences) to store the value between runs of your app.

Hmmm. If you're going to do that, then I'd definitely use a database sequence. The advantage of a time-based ID is that it's more likely to be thread safe and you can generate it independently of a database. The disadvantage, of course, is that you can't guarantee that you'll never get a collision.

Which raises a question: I've always wondered why JDBC, or databases in general, don't support an 'insert' function that returns the ID. Seems to me like the "JIT" way to go. Or maybe there is one, and I just missed it.

Winston
 
Winston Gutkowski
Bartender
Posts: 10575
66
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Mrozik wrote:A good point on the plurals - I hadn't even considered it. With most words I suppose that it wouldn't be a problem because we can just add s or es, but with the irregular words I see no other way than to just populate the database with the irregular plurals.

And this is why, like Margaret, I think:
  • (a) You need a database.
  • (b) A lot of your "relationship" logic needs to be in the database; I'd say mostly in the form of constraints.
  • Procedures are OK for really esoteric stuff, but I have to admit that I much prefer triggers (assuming your DB supports them). That way, your app simply communicates via standard SQL.

    The business of plurals is a tricky one, because you have two "either/or" forms - regular, and irregular - which is something that db's aren't particularly good at handling as relationships.

    One possible (programmatic) solution would be to simply have a "plural" column in your "word" table, which contains either:
  • (a) A suffix, denoted by a '-' in the first character: eg, "-es".
  • (b) A complete word: eg: "elves".
  • You could do the same thing with two columns: 'plural' and a boolean 'suffix?', which is probably slightly more "database-y"; but I kind of like the single column approach because it's more visual.

    One other thing: Your "lesson_has_words" table is not normalized - at least not to 3rd normal form - and the 'student_ID' is the problem. If the lesson contains the same words for all students, then student_id is redundant.

    What I think you're missing is an "Attendance" ('student_has_lesson') table. If the 'words' for a specific lesson can be different for each student, then your "lesson_has_words" table should be {attendance_id, word_id}; if not, then just {lesson_id, word_id}.

    HIH

    Winston
     
    Paul Mrozik
    Ranch Hand
    Posts: 117
    Chrome Mac Ubuntu
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Campbell Ritchie wrote:
    Paul Mrozik wrote: . . .

    A good point on the plurals - I hadn't even considered it. . . . but with the irregular words I see no other way than to just populate the database with the irregular plurals.

    What is wrong with populating the database with all plurals. It might be more work initially, but the programming afterwards may be easier. I have had such a database since I was about three years old, including the rule about adding ‑s. That database is installed somewhere in my head.


    Well there are also adjectives, adverbs, irregular past and past participle forms for verbs, etc. Take the word work, for example: You'll have work, works, worked, working, workable, and perhaps more variations.

    If it's a verb, then there's a past tense, is it regular or irregular? Should it just be in another column or a separate table on its own?



     
    Paul Mrozik
    Ranch Hand
    Posts: 117
    Chrome Mac Ubuntu
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Winston Gutkowski wrote:
    Paul Mrozik wrote:A good point on the plurals - I hadn't even considered it. With most words I suppose that it wouldn't be a problem because we can just add s or es, but with the irregular words I see no other way than to just populate the database with the irregular plurals.

    And this is why, like Margaret, I think:
  • (a) You need a database.
  • (b) A lot of your "relationship" logic needs to be in the database; I'd say mostly in the form of constraints.
  • Procedures are OK for really esoteric stuff, but I have to admit that I much prefer triggers (assuming your DB supports them). That way, your app simply communicates via standard SQL.

    The business of plurals is a tricky one, because you have two "either/or" forms - regular, and irregular - which is something that db's aren't particularly good at handling as relationships.

    One possible (programmatic) solution would be to simply have a "plural" column in your "word" table, which contains either:
  • (a) A suffix, denoted by a '-' in the first character: eg, "-es".
  • (b) A complete word: eg: "elves".
  • You could do the same thing with two columns: 'plural' and a boolean 'suffix?', which is probably slightly more "database-y"; but I kind of like the single column approach because it's more visual.

    One other thing: Your "lesson_has_words" table is not normalized - at least not to 3rd normal form - and the 'student_ID' is the problem. If the lesson contains the same words for all students, then student_id is redundant.

    What I think you're missing is an "Attendance" ('student_has_lesson') table. If the 'words' for a specific lesson can be different for each student, then your "lesson_has_words" table should be {attendance_id, word_id}; if not, then just {lesson_id, word_id}.

    HIH

    Winston


    Thank you Winston, especially for noticing the fact that "lesson_has_word" is not normalized. Yes, words can be different for each student so I'll have to think about an attendance table.

    The program I'm writing is functional as of early this morning, so I'm very happy. I really needed to get it working as it will save me time, and now I can now focus on slowing down, polishing, and most importantly learning.
     
    Rancher
    Posts: 999
    5
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Winston Gutkowski wrote:
    Which raises a question: I've always wondered why JDBC, or databases in general, don't support an 'insert' function that returns the ID.


    How to get the inserted ID in JDBC?
     
    Winston Gutkowski
    Bartender
    Posts: 10575
    66
    Eclipse IDE Hibernate Ubuntu
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Ivan Jozsef Balazs wrote:How to get the inserted ID in JDBC?

    Aha! Thanks for that, Ivan.

    Winston
     
    Ivan Jozsef Balazs
    Rancher
    Posts: 999
    5
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Winston Gutkowski wrote:
    Thanks for that, Ivan.


    You're welcome.
    I needed this and looked up and it worked.
     
    • Post Reply Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!