Should every table have Primary key ?
-
Hi I have a group of tables that have some relationship with each other . HardwareType( HardwareTypeID (pk) , Name ) Hardware (HardwareID , Model , ...,HardwareTypeID (FK)) Properties(PropertyID (PK) , Name , HardwareTypeID) Values( ValueID (PK) , ValueTitle , PropertyID (FK)) Hardware-Value( PVID (PK identity), ValueID (FK), HardwareID(FK)) with these in my design i have a circuit in my design and this is very bad for my update and delete operations . so i want to know that should i have Primary keys and relationship always in my tables or i can avoid using primary key and relationships in my design under some circumstances like the Hardware-Value( PVID (PK identity), ValueID (FK), HardwareID(FK)) can i omit the foreign key constraint in this design without loosing the normal form of my database ? thanks
-
Hi I have a group of tables that have some relationship with each other . HardwareType( HardwareTypeID (pk) , Name ) Hardware (HardwareID , Model , ...,HardwareTypeID (FK)) Properties(PropertyID (PK) , Name , HardwareTypeID) Values( ValueID (PK) , ValueTitle , PropertyID (FK)) Hardware-Value( PVID (PK identity), ValueID (FK), HardwareID(FK)) with these in my design i have a circuit in my design and this is very bad for my update and delete operations . so i want to know that should i have Primary keys and relationship always in my tables or i can avoid using primary key and relationships in my design under some circumstances like the Hardware-Value( PVID (PK identity), ValueID (FK), HardwareID(FK)) can i omit the foreign key constraint in this design without loosing the normal form of my database ? thanks
You shouldn't, the foreign keys are forcing you to insert, update and delete in the right order which will make sure you don't get any database corruption. If you are using Oracle you may read up on "deferrable constraints" where the checking is deferred until the commit. But I recommend that you keep the foreign keys.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
Hi I have a group of tables that have some relationship with each other . HardwareType( HardwareTypeID (pk) , Name ) Hardware (HardwareID , Model , ...,HardwareTypeID (FK)) Properties(PropertyID (PK) , Name , HardwareTypeID) Values( ValueID (PK) , ValueTitle , PropertyID (FK)) Hardware-Value( PVID (PK identity), ValueID (FK), HardwareID(FK)) with these in my design i have a circuit in my design and this is very bad for my update and delete operations . so i want to know that should i have Primary keys and relationship always in my tables or i can avoid using primary key and relationships in my design under some circumstances like the Hardware-Value( PVID (PK identity), ValueID (FK), HardwareID(FK)) can i omit the foreign key constraint in this design without loosing the normal form of my database ? thanks
yousefshokati wrote:
can i omit the foreign key constraint in this design without loosing the normal form of my database ?
No, it needs a primary key to be considered a normalized database. You could use the steps as described in the wikipedia to validate the model; inconsistencies should disapear when your model hits 3NF. Can you show us some example-data in those tables? It's easier to validate the model if there's a sample-population.
Bastard Programmer from Hell :suss:
-
Hi I have a group of tables that have some relationship with each other . HardwareType( HardwareTypeID (pk) , Name ) Hardware (HardwareID , Model , ...,HardwareTypeID (FK)) Properties(PropertyID (PK) , Name , HardwareTypeID) Values( ValueID (PK) , ValueTitle , PropertyID (FK)) Hardware-Value( PVID (PK identity), ValueID (FK), HardwareID(FK)) with these in my design i have a circuit in my design and this is very bad for my update and delete operations . so i want to know that should i have Primary keys and relationship always in my tables or i can avoid using primary key and relationships in my design under some circumstances like the Hardware-Value( PVID (PK identity), ValueID (FK), HardwareID(FK)) can i omit the foreign key constraint in this design without loosing the normal form of my database ? thanks
If you ignore key constraints your database will shortly deteriorate into a pile of garbage. While it is convenient during development and your business layer SHOULD cater for all your relational requirements having a rigid set of relationships in your data structure will INSURE that your database is not a pile of steaming poop!
Never underestimate the power of human stupidity RAH
-
Hi I have a group of tables that have some relationship with each other . HardwareType( HardwareTypeID (pk) , Name ) Hardware (HardwareID , Model , ...,HardwareTypeID (FK)) Properties(PropertyID (PK) , Name , HardwareTypeID) Values( ValueID (PK) , ValueTitle , PropertyID (FK)) Hardware-Value( PVID (PK identity), ValueID (FK), HardwareID(FK)) with these in my design i have a circuit in my design and this is very bad for my update and delete operations . so i want to know that should i have Primary keys and relationship always in my tables or i can avoid using primary key and relationships in my design under some circumstances like the Hardware-Value( PVID (PK identity), ValueID (FK), HardwareID(FK)) can i omit the foreign key constraint in this design without loosing the normal form of my database ? thanks
You must be careful how you create your tables. Make sure you have the right entities identified and then normalise them and create your tables accordingly.In some cases, the foreign key becomes part of the composite key(i.e. a multi-column primary key) Since I am not familiar with what you have in mind, I will use a different example. Consider a database for students, courses, and registration Student(StudentId, FirstName,LastName, DOB, SIN....) Course (CourseID, CourseName) Semester(SemesterId, StartDate, EndDate) CourseReg(StudentId,CourseID,SemesterID) The primary keys are bold and underlined.The primary key for CourseReg is a composite key made up of foreign keys. Foreign keys are not always used as part of the composite key; in general use the fewest possible columns in a composite key while maintaining normalisation(3NF is usually good enough)
-
You must be careful how you create your tables. Make sure you have the right entities identified and then normalise them and create your tables accordingly.In some cases, the foreign key becomes part of the composite key(i.e. a multi-column primary key) Since I am not familiar with what you have in mind, I will use a different example. Consider a database for students, courses, and registration Student(StudentId, FirstName,LastName, DOB, SIN....) Course (CourseID, CourseName) Semester(SemesterId, StartDate, EndDate) CourseReg(StudentId,CourseID,SemesterID) The primary keys are bold and underlined.The primary key for CourseReg is a composite key made up of foreign keys. Foreign keys are not always used as part of the composite key; in general use the fewest possible columns in a composite key while maintaining normalisation(3NF is usually good enough)
Hello . Thanks for your help .I ask this question because i had a problem in my own design I have 5 entities that are related to each other and with foreign keys that i am sure are needed < there is a Circle in my design and this circle causes anomalies in the database and for this reason database prevent the DML actions in some of my tables . I am going to ask a total question from you : What should I do to solve the problem with the circles that appear because the foreign key constraints ? thanks
-
Hello . Thanks for your help .I ask this question because i had a problem in my own design I have 5 entities that are related to each other and with foreign keys that i am sure are needed < there is a Circle in my design and this circle causes anomalies in the database and for this reason database prevent the DML actions in some of my tables . I am going to ask a total question from you : What should I do to solve the problem with the circles that appear because the foreign key constraints ? thanks
I am not sure what you mean by "circles" but I suppose you mean some sort of cyclic constraints. Before you create tables, you need to list the entities first. Entities are things tangible or intangible for which you want to capture.Also, determine what kind of information each entity should contain. At this point you're not thinking of tables. Once you have determine the entities, then think of them as tables, and then normalise them to 3NF. Since I am not an engineer, I can't figure out what the entities were to start with. So, go back to the point before you created those tables, what were the entities or objects whose information you wanted to capture?
-
Hello . Thanks for your help .I ask this question because i had a problem in my own design I have 5 entities that are related to each other and with foreign keys that i am sure are needed < there is a Circle in my design and this circle causes anomalies in the database and for this reason database prevent the DML actions in some of my tables . I am going to ask a total question from you : What should I do to solve the problem with the circles that appear because the foreign key constraints ? thanks
-
Hello . Thanks for your help .I ask this question because i had a problem in my own design I have 5 entities that are related to each other and with foreign keys that i am sure are needed < there is a Circle in my design and this circle causes anomalies in the database and for this reason database prevent the DML actions in some of my tables . I am going to ask a total question from you : What should I do to solve the problem with the circles that appear because the foreign key constraints ? thanks
yousefshokati wrote:
and with foreign keys that i am sure are needed
Based on what you posted I am sure that some are are not needed. I seriously doubt that there is any reason for Properites to have HardwareTypeID. That is just an enumeration which can stand on its own. You tie the enumeration to something else via Values and that is sufficient.