Question Looking for a solution

[vidus]

Hi all, im looking for some advice on how to structure my db.

I have a general job board that lists all of our current jobs on the go. The problem is that as we grow we want to track more and more data about individual jobs. So much data that they need tables of their own, columns and rows.

Is it feasable to have a button to create a new table for that job with some pre define fields? Good idea?

Essentially we would most likely delete these tables once the job is complete as to not fill the db with these tables.

Im open to suggestions from anyone with experience.

 

[pbaldy]

It would help to know more about the data. It would be extremely unusual to need a separate table for each job. It would also be a headache to maintain. Typically I would expect some sort of job details table, which would have a JobID field of some sort that linked back to your jobs table.

 

[PNGBill]

Just a note on Databases. Don't think they need to sorted out like you would the notes on your fridge door or the files on your office shelf.

A database table works Better when All records, that relate, are in the one Table and you will see many references to this Table holding upwards of a million records.

Your Jobs Table should hold All Jobs (Normally) and as mentioned by pbaldy, One Field can hold the job number.

Your Forms can be set to just show the data for that One Job you want to review and you have no idea that the full list includes 10's of thousands or more Job Records.

Please don't mis read my explanation about the One Table called tblJobs to mean your Database will only have One table.
It will have a few to many tables and each of these will hold Distinct data as different to Selected Data.

 

[vidus]

I think I have come up with a solution, just have another table with ALL of the material data etc. I was just worried beacause it will have like 50 records per job... and in 5 years we may have 5000 jobs... is this ok?

 

[PNGBill]

5 years time one table will have 250,000 records. Not that big.
Someone posted saying they had 6 million and climbing.

Advice is to post more questions as you determine your tables, names, fields and relationships.

Advice sort now will save a lot of headaches later.

Some of the data you may put in your Jobs Table may well be better in a different table.
eg, Jobs Table should not include the Clients Name, Clients Address etc and shouldn't include the name of the Team Member managing the job.
Also, shouldn't hold the Hours worked on the job or the total amount charged.

 

[vidus]

5 years time one table will have 250,000 records. Not that big.
Someone posted saying they had 6 million and climbing.

Advice is to post more questions as you determine your tables, names, fields and relationships.

Advice sort now will save a lot of headaches later.

Some of the data you may put in your Jobs Table may well be better in a different table.
eg, Jobs Table should not include the Clients Name, Clients Address etc and shouldn't include the name of the Team Member managing the job.
Also, shouldn't hold the Hours worked on the job or the total amount charged.

Indeed, i feel the jobs table is appropriate. we have a quote number, job number, customer name, and some other small data for the office. Nothing too in depth.

 

[PNGBill]

As I mentioned, Jobs Table would not normally hold the Customer's actual Name.

 

[vidus]

As I mentioned, Jobs Table would not normally hold the Customer's actual Name.

why not? its relevant info.. its not like we have the address in there.

 

[vbaInet]

He means it will hold the Customer's ID, not the name.

 

[vidus]

He means it will hold the Customer's ID, not the name.

correct. its a business name always, never people.

 

[PNGBill]

The field CustomerID will hold records like 146, 147, 148 etc

Not John Smith, Fred Jones

 

[vidus]

The field CustomerID will hold records like 146, 147, 148 etc

Not John Smith, Fred Jones

so your saying that a text field is restricted to numbers only? ya... ok

there is absolutely no reason i cant put things like "target" or "mcdonalds".

 

[vbaInet]

That was just an example of the Customer IDs. Your ID could be a text field if that's what you wanted. But when you say customers, do you mean clients? So you could have a client id for Mc Donald's to be "MCD01", but not an ID like "Mc Donalds".

 

[vidus]

That was just an example of the Customer IDs. Your ID could be a text field if that's what you wanted. But when you say customers, do you mean clients? So you could have a client id for Mc Donald's to be "MCD01", but not an ID like "Mc Donalds".

its just a field called [customer] with entries such as "mcdonalds"


there is nothing wrong with that

 

[vbaInet]

So let's say you have a company called "North Atlantic Treaty Organisation", what would the ID be for that?

 

[vidus]

So let's say you have a company called "North Atlantic Treaty Organisation", what would the ID be for that?

wait, are you thinking im using the primary key id column as a customer field? where did you get that idea?

as i said multiple times, this is a TEXT field, that holes business name. NOT an int field, which would not allow letters. I dont know where you came up with that idea.

 

[pbaldy]

There's nothing "wrong" with it, but it's generally considered a bad idea. Sooner or later you'll get a duplicate customer name. What are you going to do when the "Target" florist becomes a customer? As vbaInet points out, do you really want that long monstrosity as your key value? Customer names, employee names, etc are not a good choice, in my view. State/province abbreviations, sure; not names.

 

[vidus]

There's nothing "wrong" with it, but it's generally considered a bad idea. Sooner or later you'll get a duplicate customer name. What are you going to do when the "Target" florist becomes a customer? As vbaInet points out, do you really want that long monstrosity as your key value? Customer names, employee names, etc are not a good choice, in my view. State/province abbreviations, sure; not names.

as i said, its just a text field...

 

[vbaInet]

You may also find these links a good read:

http://r937.com/relational.html
http://support.microsoft.com/kb/100139

 

[PNGBill]

All the communication so far, to my knowledge, is talking about your Jobs table, not your Customers table.

In your jobs table, you will have a primary key field and other fields that relate to the job.
One, only one, of these other fields will be called CustomerID (or similar) and this would hold either 123, 457, JH2, or some other (possibly meaningful) short text or number data.

The Customer Name ie, John Smith, will be one, 2 or 3 fields in the Customer Table, a completely different table to Jobs Table.
The Customer Table will also have a primary key of text or integer but if text, then limited to 4 or maybe 6 chrs.

If this is not what you have in mind, then I suggest you respond with some sample names for your primary keys and table names and or study the links provided or you can expect some more posts down the track as things start to turn to custard.