Question of Table Design and Foreign Keys
От | David Pratt |
---|---|
Тема | Question of Table Design and Foreign Keys |
Дата | |
Msg-id | 243B812C-D1D5-11D9-AEE7-000A27B3B070@eastlink.ca обсуждение исходный текст |
Ответ на | Starting postgres server second time using admin rights... (rohan rasane <rohan.rasane@gmail.com>) |
Список | pgsql-admin |
I have a few tables that I want to join. My example is below. What I am trying to figure out is what is better to do for data integrity. I will be joining the samples table with the sample_attributes table to obtain the attributes of a sample. The attribute id will reference an attribute in the attributes table. When a person adds a record in samples, they may not want to add attributes right away. I will be setting up Foreign Key constraints for sample_id and attribute_id in the sample attributesjoin. Question is if a person does not want to add attributes to their sample record right away there will be no reference to it in sample_attributesjoin. Is that ok ? Or should use a trigger to always make at least one record in sample_attributesjoin after a sample record is made and not place a Foreign Key constraint on attribute_id in sample_attributesjoin so it can be empty. I guess I am wondering what is better. Regards, David For example; CREATE TABLE samples ( id SERIAL, title TEXT NOT NULL, description_id INTEGER, type_id INTEGER, language_id INTEGER, notes_id INTEGER, created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), modified TIMESTAMP WITH TIME ZONE ); CREATE TABLE attributes ( id SERIAL, attribute VARCHAR(50) NOT NULL, description TEXT, created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), modified TIMESTAMP WITH TIME ZONE ); CREATE TABLE sample_attributesjoin ( id SERIAL, sample_id INTEGER NOT NULL, attribute_id INTEGER NOT NULL );
В списке pgsql-admin по дате отправления: