Database design question

Question

How can a user be found using the following properties providing that user do not have any id

• Name
• Father's Name
• Mother's Name
• Date of Birth

• Sex

  Remember that user may make mistake while typing :(

Please share your ideas....

Answer 1

Dont do this way. Still if you want to go with this way only, use like syntax in your sql query. I think it will be much more helpful for all if you can provide more information about PROBLEM.

Answer 2

What database are you working this from? If a ORM is involved then you can simply code a function to search for possible cases.

Answer 3

Users make mistakes while typing... but not when typing their own names.

try searching for your man this way:

SELECT * FROM <your_table_name_here> WHERE name LIKE <name you are looking for>

This will return all the rows with that name, if you like to filter on more subjects you can add

AND dateOfBirth LIKE <the day of birth>

To the end of your query

Answer 4

This is as much a question of User Interface design as database design. But first, you must have something in the database that is unique, either a combination of fields, or a unique id. In your case I can see two alternatives. Demand that usernames be unique. If you have two John Smiths, then one of them has to adopt a uniquifier - John (Big John) Smith. Or you have an id column. You never need to show the user the id column, but you may find it useful for your implementation

Now for the UI: You've got a load of records in the database, the user wants one particualr record but only partially knows the matching data. You will need to deal with some uncertainty and interact with the user.

What do you know?

His name is Davdi and his father's name is Gordon

(search) We don't have anyone called Davdi

Oh I meant His name is David and his father's name is Gordon

OK, we've got 10 records that match that, which one do you want?

This one (user picks from list - behind the scenes we know the unique id)

Now, depending upon the database you are using you can go further. For example the user could use wildcards or regexps to specify what they mean.

His name is Dav*  (can't remember if it's Dave or David)

But the query language you are using will need to support such things. Some databases have "Sounds like" capabilties. So "Lewis" would match "Louis" and "Lewes" and even "Lou". If you really want to be helpful then you may need that kind of capability.

Answer 5

Hello everyone. Such a quick response from you all. Thank you.

But one more thing... People may make mistake intentionally (They have their own reasons!). I can not provide them unique id :(