I am trying to set up a database program (Access 2003) with the following requirements: One table has owner information for multiple owners and the other table has property information for multiple properties. Each owner has a different ownership percent of each property. What do I need to do now to track the percentage ownership that each owner has for each property? Do I need a third table? If so, what is the primary key of the third table?
Question getting started
- Thread starter feigs
- Start date
- Local time
- Today, 10:52
- Joined
- Aug 30, 2003
- Messages
- 36,157
This sounds like the old surrogate vs natural key debate. Your natural key is a compound key composed of owner ID and property ID. Your surrogate key would be an autonumber field or something similar. Supporters of each will make good cases, so I say go with what makes you happy.
FoFa
Registered User.
- Local time
- Today, 12:52
- Joined
- Jan 29, 2003
- Messages
- 3,653
Quite frankly, if it were me, I would place an autonumber field on each table and set that as your primary key.
I would create a third table that;
1 has it own autonumber primary key (just for grins).
2 has a foreign key to the owners.
3 has a foreign key to the properties.
4 has a percentage field.
In this fashion everything can be maintained in sync.
Plus you can run validations against property to verify it has 100% ownership (not over and not under).
You can verify if someone tries to add a owner to a property, they do not exist already.
Etc.
I would create a third table that;
1 has it own autonumber primary key (just for grins).
2 has a foreign key to the owners.
3 has a foreign key to the properties.
4 has a percentage field.
In this fashion everything can be maintained in sync.
Plus you can run validations against property to verify it has 100% ownership (not over and not under).
You can verify if someone tries to add a owner to a property, they do not exist already.
Etc.
- Local time
- Today, 10:52
- Joined
- Aug 30, 2003
- Messages
- 36,157
Now all we need is a natural key advocate and we'll have the debate I mentioned. 
This debate will never end. This will answer some questions and start some new ones:
http://r937.com/20020620.html
Bottom line is that everything FoFa mentioned is possible with whichever method you choose. I don't have a strong feeling either way, so I'll stay out of the debate.
This debate will never end. This will answer some questions and start some new ones:
http://r937.com/20020620.html
Bottom line is that everything FoFa mentioned is possible with whichever method you choose. I don't have a strong feeling either way, so I'll stay out of the debate.
