> > Sergei > > > > Maria pointed out that if we already have amp and fovid columns, > > then we can build a composite key and given that it is unique, > > it can be easily used as the primary key for that table, > > I understand that anything unique and not null can be made a primary key. > But is there a reason to prefer (fovid+amp) over id, as the primary key?
1 - you are saving 8 bytes per row in a huge table. As I understad the id got to be unsigned bigint because bigint doesn't cover the range, does it? 2 - In addition, the fact that your primary key is on fovid + amp implies you get two important columns indexed for the price of one. Which means better performance in searches and JOINS with those columns. > > which means that we do not need the id at all. > > Not sure this is true: id is used to trace the lineage of sources: each > source "knows" the id of the AmpImage it came from. Yes, one could also > create a foreign key to reference (fovid+amp) for that purpose, but is > this better? That is actually what I did in the SQL Server schema. I changed imgid for fovid and ampID. ( 2 more bytes saved ;-) ) By the way, foreign keys are wonderful to mantain the referencial integrity (the DBMS checks things out for you) but you still can "trace the lineage of sources" even if you don't have a foreign key constraint defined. The thing that really matter in terms of performance is that the columns are indexed. Maria _______________________________________________ LSST-data mailing list [email protected] http://www.lsstmail.org/mailman/listinfo/lsst-data
