Top/Max/Subqueries ... losing my mind
- Thread starter Cait
- Start date
Ok, here it is: business logic, current design and possible design. Hopefully this makes sense as I've been putting it together in the presence of a grumpy, chicken pox-covered five-year old. 
I would upload the database, but I'm bound by the DPA.
If this is better placed in another area of the forum, I'm happy to do so.
I would upload the database, but I'm bound by the DPA.
If this is better placed in another area of the forum, I'm happy to do so.
Last edited:
dallr
AWF VIP
- Local time
- Today, 12:53
- Joined
- Feb 20, 2008
- Messages
- 81
Hello Cait, please forgive me for not posting on the day i said i would post. I was working on a project and had a very tight timeline.
I am a man of my word and i promised to help. The project was finished 2 days ago and will to assist.
So, do you still need help on your table structure?
dallr
I am a man of my word and i promised to help. The project was finished 2 days ago and will to assist.
So, do you still need help on your table structure?
dallr
dallr
AWF VIP
- Local time
- Today, 12:53
- Joined
- Feb 20, 2008
- Messages
- 81
Hello Cait, I looked at the information you provided and have some comments. I started to build the data model but I wanted some feedback to clarify certain issues.
1. You mentioned that you wanted to track contractors that and the amount of times they visited a location. I think this should not be done on a selective basis but captured all the time if you can but this is up to you.
2. You spoke about tenants briefly but how do they fit into the whole scheme of things? I suggest this will have to be modeled as a one to many (1-M) with the one side being the property. I would suggest that the tenants table should also have information on the date entered and the date exited.
3. What is PBTC stand for and its purpose?
4. Certain fields need to be atomic. Example: the Ho_Name should be broken down into firstname, LastName. The contractor_Name might need to be broken down to an atomic value, well that is if it is a person. If it is a company name then it is just fine.
5. Can a contractor who is assigned to a service be changed? And if so do you keep a history of the previous contractors who were assigned?
6. What triggers a contractor to start a new service? What I mean is, let’s say the first time a property was serviced was on the 1/Feb/2008. We know it must be serviced within 365 days, but after that what triggers the next 365 days service. It is that once you assign a property to a contractor it is theirs to service whenever they feel once it is within the 365 days going forward? I hope you understand what I am asking here.
See attached database for what was done so far, please review in detail and comment accordingly.
dallr
1. You mentioned that you wanted to track contractors that and the amount of times they visited a location. I think this should not be done on a selective basis but captured all the time if you can but this is up to you.
2. You spoke about tenants briefly but how do they fit into the whole scheme of things? I suggest this will have to be modeled as a one to many (1-M) with the one side being the property. I would suggest that the tenants table should also have information on the date entered and the date exited.
3. What is PBTC stand for and its purpose?
4. Certain fields need to be atomic. Example: the Ho_Name should be broken down into firstname, LastName. The contractor_Name might need to be broken down to an atomic value, well that is if it is a person. If it is a company name then it is just fine.
5. Can a contractor who is assigned to a service be changed? And if so do you keep a history of the previous contractors who were assigned?
6. What triggers a contractor to start a new service? What I mean is, let’s say the first time a property was serviced was on the 1/Feb/2008. We know it must be serviced within 365 days, but after that what triggers the next 365 days service. It is that once you assign a property to a contractor it is theirs to service whenever they feel once it is within the 365 days going forward? I hope you understand what I am asking here.
See attached database for what was done so far, please review in detail and comment accordingly.
dallr
Similar threads
