Database Design: An Alternative to Composite Keys?
I am creating a database system and am having problems with the design of one of my tables.
This system has a user table, an object table, an element table, and a cost table.
A unique entry in the cost table is defined by user, object, item, and year. However, there may be multiple entries that have the same year if the item is different.
Hierarchy: user-> object-> item-> year, several unique years for each item, several unique elements for each object, several unique objects for each user, several unique users.
What's the best way to create a cost table?
I am thinking of including userid, objectid and itemid as foreign keys, then using a composite key consisting of userid, objecid, itemid and costyear. I've heard that composite keys are bad design, but I'm not sure how to structure this to get away from using a composite key. As you can tell, my database building skills are a little rusty.
Thank!
PS If it matters, this is the db interbase.
source to share
To avoid a complex key, you simply define a surrogate key. This has an artificial meaning, for example an auto counter.
You can (and should) define a unique constraint on these columns.
Btw: its not only recommended to use composite keys, but it is also recommended to use surrogate keys. In all your tables.
source to share
Try to create your database with a composite key using only the columns you specify and see what happens. You will be pleasantly surprised. Ensuring that there are no missing data in these four columns and making sure that the two rows have the same value in all four columns will help protect the integrity of your data.
When you declare a composite primary key, the order of the columns in the declaration does not affect the logical consequences of the dclaration. However, the composite index you create for your DBMS will also have columns in the same order, and the order of the columns in the composite index affects performance.
For queries that only specify one, two, or three of these columns, the index will be useless if the first column in the index is a column not specified in the query. If you know ahead of time how your queries are chasing me, and which queries most often need to run quickly, this can help you declare your primary key columns in the correct order. In rare cases, creating two or three additional column indexes can speed up some queries by slowing down the updates.
source to share