Database Design

[bjblackmore]

Hi,
I have a table in my database for user information. At 1st I was thinking of only storing basic user info, username, 1st name, last name, password etc. However I now want to add more advanced user details, like 2 or 3 comment fields, address, directions, nearest facilities.
Should I create a new table, and have user_basic and user_advanced. Or just stick it all in 1 table?
The tables haven't been used yet, and are blank so wiping them, and starting again isn't a problem.
Cheers for any input
Ben

[[deleted]]

Did you read the articles about database normalization on this site?

Only create a new table if there is a one-to-many or many-to-many relationship.

For the users, there is a one-to-one relationship with the adreses and names, so no extra table is needed for the adresses.

The basic/advanced can be a simple integer in a column. if it's 1, it's basic, if it's two it's advanced.

[Anon]

While I agree with Vincent, there may be other issue to consider:
1)How will that data be accessed?
ex: if you are going to be getting data from your first table often and only getting data from the other info very infrequently, it may make sense to have two separate tables. Or if you will be getting data in different contexts, it might make sense to have two tables just for clarity of organization: i.e. an "address book" table and a "physical characteristics" table have different purposes, but have a one-to-one relationship. Since you would be using the data in different ways, it might make sense to have two tables.

2)Are there security issues that need to be addressed?
ex:having separate tables allows you to vary access rights for different types of users: i.e. allow everyone to access the main table (edit/read/etc), allow only "special" users access to the second table.

In general, tables with a one-to-one relationship are inadvised, but in some cases useful and appropriate.

Vincent is a more experienced programmer than I, so I defer to his judgement, but these are some additional things to think about.

[[deleted]]

1) About accessing some data more frequently than others: there is hardly any speed difference between fetching from a 4 column table and a 40 column table, unless those columns contain BLOB fields (ie: they are VERY large)

About using multiple tables for clarity: Valid point, storing data that is 'very' different in seperate tables can be a good thing. It really depends on how different the nature of the data is.

Remember that every time you split data up, you are going to have to merge it lateron, so don't be too split-happy.

2) Good point.
Many people use access-rights at PHP level. We logon to a PHP page, and depending on some variable we can do certain things.
That's cool, but not fullproof. Most of us have an account on our MySQL
server that has 'root' rights on the database, so it can do everything (create/drop/select/insert etc).
Sounds ok, but it's not. The account used by your script should not have any more privileges than it needs. That way, if you accidently F-up, and put a 'drop database' in a query, the database will refuse to execute the query because the account you used did not have the privileges to do it. Instant failsafe.