Directory-based people database

[Paul Mrozik]

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?

[Stuie Clarky]

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

[Winston Gutkowski]

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]

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.

[Jeff Verdegan]

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]

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.

[margaret gillon]

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]

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.

[Campbell Ritchie]

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]

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]

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]

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]

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.

[Ivan Jozsef Balazs]

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]

Ivan Jozsef Balazs wrote:How to get the inserted ID in JDBC?

Aha! Thanks for that, Ivan.

Winston

[Ivan Jozsef Balazs]

Winston Gutkowski wrote:
Thanks for that, Ivan.

You're welcome.
I needed this and looked up and it worked.