Storing notes in database

dragon_sa

What would be the best way to store notes using a database, first of all here is the scenario:-

Project system - user logs in - views current jobs - views notes for particular job - add note to job - view notes etc.

Client can have more than one job in the system at one time, and there will be several clients both current, past and recurring, user logged in is a staff member accessing client job information.

Would i use a seperate note database for each client and then identify each note by a job number, or would i just have one database for all clients notes, I am worried about size each set of notes for each job may get, yet some may have few, so what do you beleive would be the best way to store and retreive them for me, thanks so much for your advice in advance 🙂

Piranha

You should have one database with all the clients. Then you use foreign keys to connect notes with jobs, jobs with users and users with projects. Don't worry about the size of the database, it won't be a problem. If we use this forum as an example. There are at least 16 subforums, 200.000 threads, 850.000 posts and 90.000 members. And it runs without problems. And you could easily handle 10 times that, or 100 times that, in one database. To make it simple: Size normally doesn't matter when it comes to databases.

I suggest that you develop a database design and let us take a look at it. Then we can help you out if something is strange. Just remember to give as good description as possible of what you want the database to do, otherwise it won't make a sence for us to check it out.

dragon_sa

Currently I have a users table which stores a users information such as address and contact details and also user type(staff, client, supplier, admin, manager)

I also have a contracts table, which stores which client user a contract is with, the ***job number, and details when to start etc and its current status.

The notes table I am planning to have linked to each contract using the ***job number, then date note added, and note added by user and of coarse the note itself.

would I create a new notes table for each client user and store all that clients job notes there for all jobs?

do i store all notes in one table and just link as I mentioned before by the job number which will be unique to each contract?

***job number
would also like some guideance for creating this job number automatically, ie, in the format clientname-000001, clientname-000002, clientname-000003 etc etc per client contract, i would like to use only up to the first 6 characters of a client name then the incrementing number for each contract.

Thank you for your fast reply Piranha

NogDog

Option #2.

The simplest thing with the job number would be to just use an auto-increment integer field, then dynamically generate a specific job number as needed by concatenating the client name with that number, perhaps via a php sprintf() function. The downside is that they will not be consecutive for a given client, e.g., they might be:

clientA-000001
clientA-000002
clientB-000003
clientC-000004
clientB-000005
etc....

If they have to be separately sequential for each client account, then you'd probably have to do something clever with a sub-query in the insert statement when creating a job entry, doing a select on the MAX() number for that client, and adding 1 to it. This could be a bit problematic should there be any possibility of concurrent entries for the same client, perhaps requiring table locking to ensure no duplications (thus another argument for simply using the auto-increment field described above.

Piranha

You store all notes in one table and link to the job number. If the notes is specific for one user you link to that user as well as to the job number. Just make sure that you set the foreign keys as indexes to make searching faster. They normally are that automatically, but check to make sure.

But there is a possible problem with your database structure. What happens if I am both a supplier and a client, would I get two rows in the users table in that case? What if I move, would both the rows automatically be changed with my new address? There is a big risk that something will go wrong.

To avoid this normally you create one database with general user information and another with the role, using a foreign key in role to link the users table. Then you link everything that is role specific to the role table and everything else to the user table.

I'm not saying that you should change it if it works as it is now. But you should think about it next time you create a database to avoid future possible problems.

dragon_sa

Thanks nogdog 🙂

Good point piranha about the client and supplier possibly being the same, and I have come to realise that the supplier doesnt need to be a user in the system and will set up a seperate supplier table for storing their details, as they will only be referenced for quoting purposes, then i will set up a seperate table similar to the notes table to list all the products they supply.

Currently a user gets associated to the open contract form by being listed as a client in the user table, when a contract is opened the user business name is logged in the contract_to field in the contract table, and a job number is created using the client business name as stated preiviously and is logged in the job_number field.( I will open a new thread for the job number create bit )

question - do I use this job number field as the foreign key as it is unique to each job and will auto increment for each client by one, also could you give me an example of how to do this and set it for indexing with an sql statement?