I have designed my database using a somewhat oriented approach. Rows are objects, and different type of objects are in different tables, but since my objects share a common set of fields like ExpireDate, Archived, Draft, CreateDate, CreatorID, etc; I have an 'objects' table with these columns, and I have set up a foreign key in other tables where there is a need for a row-to-row integrity.
Now my question is, since I have to SELECT using INNER JOIN with object and the corresponding table, merely to filter out archived rows, I am wondering if I would be better off actually putting the common fields into each respective table and get rid of the 'objects' table altogether. I mean, is it more job for the server to actually JOIN the tables for each SELECT versus having a clean design with object oriented approach ?
How bad is my design, and what are the recommendations of experts who obviously were tempted to create an object oriented database design ?
Using Join for each Select will surely be a over head for the server especially if the number of rows in each table is very high,
Clean table design and performance are usually like tow ends of a coin. Its not possible to have both.
So analyse your requirements. If the select query with join is going to be used very rarely then u can proceed with your current design, on the other hand if it is going to be executed many times then it is better to avoid the Join.
Again, this is just my own opinion, analyse ur requirements and then do any change in your design
Well, the query is going to request the forum posts and there will be many of them to choose from, yet I see the situation of duplicaing the common fields into each table as rather stupid thing to do. Am I wrong ? Should I duplicate the fields into each table, and get rid of that INNER JOIN ?