This content has been marked as final. Show 5 replies
Along with making the above relationship normalized, I also want to maintain change history. Please see if my below model looks ok, and please advise on what corrections I need.
I suggest you create some sample data, some sample tables and put the data in the tables and then see if what you have is workable.
For example, you have a 'Person' table with a person-id that is 1 to many to person-name.
So the same person-id can have different names?
But then you say than the 'name' table is also 1 to many to person-name.
It looks like you are trying to keep a separate history table for each individual attribute of an entity.
A person is a person is a person whether they are a 'contact' or not so why are you treating contacts separately from 'person' instead of as an attribute of person? Your friends are 'persons' too aren't they, even if they are also contacts?
ssk1974 wrote:All you've shown are table and column names. Normalization isn't about what the tables and columns are named; normalization is concerened with what the tables and columns contain, and how they are related to each other. Before anyone can know whether the design is normalized or not, or whether it's good or bad in any other respect, they have to know what business reality is being modelled, what the application is trying to do, how will it be used, and how each table and column helps the application work.
I currently have the following database design and trying to make it normalized.
Table Name Columns
Program Program-ID, Program-Name, Program-Info, Person-ID(FK to Person)
Person Person-ID, FName, LName, Addr1, Addr2, City, State, Zip, Phone1, Phone2, Email
For example: What does the person_id column in the program table represent? Is it the director of the program (and each program can have, at most, one person)? If so, that part looks good. Is that column supposed to be showing participation in programs, where many people can be participate in the same program? If so, it doesn't look so good.
Along with making the above relationship normalized, I also want to maintain change history. Please see if my below model looks ok, and please advise on what corrections I need.Do you really have 9 different tables just to handle change history for the 2 tables containing current data?
Table Name Columns
Program Program-ID, Program-Name, Program-Info, Effective_Date
Person Person-ID (1 to Many to Person-Name ) (1 to Many to Person-Contact ) (1 to Many to Person-Address)
Name FName, LName, Effective_Date (1 to Many to Person-Name )
Person-Contact Person-ID, COntact-ID
Contact Contact-ID,Phone1, Phone2, Email, Effective_Date (1 to Many to Person-Contact )
Person-Address Person-ID, Address-ID
Address Address-ID,Addr1, Addr2, City, State, Zip, Effective_Date (1 to Many to Person-Address)
Program-Contact Program-ID, Person-ID, COntact-ID, Address-ID (1 to Many to Person-Address) (1 to Many to Person-Contact)
If I have one to many to Person-Contact is that enough to get the name too?Sorry, this question is unclear.
Describe what business entities are involved, and how these entities correspond to tables.
Describe what attributes are associated with the entities, and how these attributes correspond to columns.
Describe what the application will do, and how the tables and columns you've planned will help it do that.
sb92075 wrote:Database designs/data models are SQL indifferent. SQL is about implementing and using a data model. Not about whether the model's design is sound.
We speak SQL?
Do you speak SQL?
Please post CREATE TABLE statements.
SQL is irrelevant in this case. CREATE TABLE statements are premature.