IWETHEY v. 0.3.0 | TODO
1,095 registered users | 1 active user | 1 LpH | Statistics
Login | Create New User

Welcome to IWETHEY!

New DB design questions
Setting up a DB for tracking police incidents. Primary table is incidents. One of the child tables is people. If the same person keeps showing up, we'll want to know that. Couple questions.

1) Should I try to set a unique key (last-first) on the person table? The alternative is to just sort the table by last name and look for matches.
2) I'm using OpenOffice Base, but I assume this will be similar in other tools. From the parent form, I'll be using a subform for people. Is there a way, when I start typing in a name, for it to do a lookahead and autocomplete from existing names?
3) Should I use a foreign key from the child table in the parent, or use a junction table?

Similar issue for addresses. But for that one there's an additional question.

1) I want to use a unique key (zip-address-unit) but unit is an optional field. Can you do a unique multi-col key when the last column in the key is optional?

New No answers, but...
Remember that some names are so common that duplicates or more are possible. So "last, first" won't cut it.

This is back a while, but I had a friend named Paul Smith who was visiting the White House on a special tour. It took him over an hour to get through the clearance process because as you might expect that even with middle initial or even middle name there were many "hits".

"There is a cult of ignorance in the United States, and there has always been. The strain of anti-intellectualism has been a constant thread winding its way through our political and cultural life, nurtured by the false notion that democracy means that "my ignorance is just as good as your knowledge."

-- Isaac Asimov
New This, plus...
Caveat: I haven't used Base to do anything serious with.

I think the normal behavior for a data drop down is to load all values that meet the specs (table, query, raw SQL) and typing then advances the cursor to the next matching value. You may be able to come up with a type ahead lookup by setting up a keyboard event handler attached to a drop down which uses a query to populate itself. (i.e. modify the "where" clause as the user types the name.) Either way, scalability is limited.

The need for a junction table depends entirely on the overall data model. If there is no many-to-many relationship between two models, then a junction table would allow one to construct things that should not be possible.

Base does not allow nullable fields as part of the primary key, but an additional unique constraint can contain nullable fields.
New Ah, I tried it as the primary.
I didn't think to do it again as an additional constraint.

Thanks for the feedback on duplicate names. Yeah, I don't think I can solve that. The addresses I should be able to do.

There's no way around one person entering "Fifth Street" and another entering "5th St." But that's why I'm trying to do some auto-complete magic. Make it easier to do things right, and they're more likely to do it.

New Fifth/5th...
In DC there are a bunch of lettered streets.

I and J and U caused problems...

Is this a national database, or it it restricted to local roads? If it's national / regional, you might want to look at how the USPS standardizes addresses. This must be a solved problem, and you might save yourself some work in seeing how they do it - I think they even have an API that other sites can use.

Or maybe not - I'm no expert. But it's an interesting problem!

Good luck.

New Dont forget jr, sr, III, etc living at same home
Major issue in my junk mail career was unique id of people.
New I need to be less ambitious
I've done name and address standardization and matching before. I know how hard it is to do well, but for some reason was forgetting all that and jumping straight to UI work. Idjit.

We've only got two days to do this. What we need is a good data model and user-friendly data entry. Once they've got data, they can clean it up via USPS API in the future.

New DB design answers:
1) No, since the values won't be unique, you can't have a unique key. You can have an index (or several) to help with the sorting.

2) Dunno, never used that.

3) This isn't a one-to-many or a many-to-one relationship either way, is it... You can have the same people in several incidents? And several people in the same incident? Yup, for many-to-many relationships you need a "junction" table, like "people_and_incidents". Two columns, "person_id" and "incident_id", foreign keys to the (primary-key) ID columns of those tables.

1) Nope. At least, not unless you put a default value ("N/A" or something, whatever) in the "Unit" field. But then it's of course not really optional anymore.

But, above all: The main thing you need to do is put an integer ID field -- autoincrement, sequence, whatever it's called in your (R?)DBMS -- in pretty much every table. That's your primary key, and what you refer to as a foreign key from other tables.
Christian R. Conrad
Same old username (as above), but now on iki.fi

(Yeah, yeah, it redirects to the same old GMail... But just in case I ever want to change.)
New I should have mentioned in the request, this was for an event 7/21 - 7/23
So I appreciate the reply but yeah, it's already done. :)

New Ah. On vacation, been kind'a offline for a week or so.
     DB design questions - (drook) - (9)
         No answers, but... - (a6l6e6x) - (5)
             This, plus... - (scoenye) - (4)
                 Ah, I tried it as the primary. - (drook) - (3)
                     Fifth/5th... - (Another Scott)
                     Dont forget jr, sr, III, etc living at same home - (crazy) - (1)
                         I need to be less ambitious - (drook)
         DB design answers: - (CRConrad) - (2)
             I should have mentioned in the request, this was for an event 7/21 - 7/23 - (drook) - (1)
                 Ah. On vacation, been kind'a offline for a week or so. -NT - (CRConrad)

Cry havoc! And let slip the dogs of war!
70 ms