How far do you split your tables

[hargi]

Looking at a database app.

Say for example you have a table called person.
So person as attributes of
FirstName
LastName
DOB
Gender
MaritalStatus
Disabilities

for example

Im thinking that Disabilities should be another table as in one person can have many disabilities and a disability can be linke to many people so some join table will be needed.

However, gender, im assuming this is a one to many relationship from my understanding one person can have one gender but gender can be assigned to many people. But is it worth breaking it down to this level?

Thanks

 

[bridder]

If you think you may want to report on it I would, actually I probably would anyway. What if someone enters Mr mr and MR, or Male, M and m, I suppose as well that it depends upon how you are exposing that field, if it is via a dropdown list that you have control over;

Dropdown list
Text: Male and Female
with values
Values: M and F

You will get consistency and be able to report on it, if it is a textbox that a user types into then you are asking for typos.

I'd split them off into another table.

J.

 

[RoyMi6]

You're kind of crossing two database theories here.

One is "normalisation" and the other is ensuring your data is "atomic".

Generally normalisation means ensuring the data is grouped together logically. I.e. you have a table called person that has the attributes listed above but would not include their purchase history.

Specifically the question you ask is to do with the data being atomic - i.e. broken down into the smallest sensible parts.

For example it usually makes perfect sense to break down the address field into the general address and a separate column for country and postcode because these fields can be used for other things.

So, for your example if you're looking to store a list of many disabilities (and the front end lets you select from a preset list, maybe even add new ones) then yes, you'd want to split it out if you're doing any logic associated with that information (e.g. show me a list of everyone that has diabetes)

For gender it depends how you use it. If you just want to ask are you male or female then you'd get away with a boolean column called "Male". However, gender is not that simple if you work in the medical practice (as I once set up a website to deal with) where we had to ask if their gender was of a variety of transgender options (male to female, female to make, post op, pre op etc - I forget the exact terms). So in this case (if it matters) yes, make another table with Gender Types that your application can list and people can choose from. Again, this will allow easy querying for the database for "all people that are 'transgender, male to female, post op'".

EDIT:: Oh, also, appreciate it may just be a proof of concept development or something but obviously data security and adhering to all data protection practices starts to become more important when asking this kind of personal information.

 

[hargi]

Thanks both for the replies. That makes sense to me now.

RE data protection and security I have read http://www.ico.gov.uk/for_organisations/data_protection/security_measures.aspx

Now if this database was going to be stored on a web server with a php ( codeignite ) front end.
The site would have to be https with ssl?
The database would have to be secured in a 'members only area' so a few select people would have to log on with there own username and a strong password.

 

[RoyMi6]

What you need to do ensure is that all your sensitive data is sent over the internet securely.

It can be overkill to secure your entire website (why encrypt your homepage for example?) but it can often just be easier to do so.

The disadvantage of this is that https traffic is never cached and takes up more processing power. The more processing power is not an issue for a small sites so often they take the easy approach and have no problems. Sites like Facebook on the other hand put off implementing SSL for a number of years before they could find a way to cope with the overhead it would cause.

Keep in mind as well that your database isn't only accessed via the front end but also the back end. Hackers that gain access to your (possibly) shared hosting environment can look directly at your data. This is why it's so important to sensibly encrypt passwords and credit card details. You might want to see if the medical information you're collecting falls into the "highly sensitive" personal information category. Not sure if there's a definitive list on this....

For your website it sounds like a unsecured www.website.com domain with a SSL secured members.website.com subdomain would be the way to go.

If however your main, publicly accessible website is minimal you might want to just put everything under a secured www.website.com domain. Only you know your potential market / traffic / workload that each user will bring and can make that call.

 

[Gornall]

SQL injection (assuming the database would be SQL based) is also something to consider when making a database that can be queried online.