Is this an OK Database design concept?

Question

Hi folks,

EDIT1: Tried to clear the question up by renaming the tables AND their relationships. EDIT2: Please don't look at the what TYPE of data i'm holding in the three DB tables. They were made up on the fly. They are NOT my real world scenarios (and no, I can't talk about my real world data .. in fact it's 1 parent and 6 children, currently). Please just ignore what type of data and just look at the fact that some data is required. EDIT3: The two FKs are a 0 or 1 to 1 relationship. NOT 0 to many. Not 1 to 1. I'm trying to avoid the 0 or 1 to 1 relationship to a 1 to 1 relationship so i don't need to have OUTER JOINS but instead have an INNER JOIN.

Question: I need to know if the proposed database design is good/bad/lame/etc..

Problem: today i tried to make an indexed view, but failed 'cause my tables have outer joins. Sigh. So i was wondering if i can refix this up to be like the following design:

• Three tables.
• table_User has a FK on table_Address
• table_User has an FK on table_Vehicle
• etc..

and table B and C (which sorta act like lookup tables now) have..

• Id INT IDENTITY PK
• Description NVARCHAR(100) NULLABLE

notice the nullable? this way, something in table_User doesn't exist in table_Address ... the the field is null (because of the inner join).

Before, i made that an LEFT OUTER JOIN, so if there was no data in table_b, i'll get nulls are the result for each field.

I'll throw some data examples here...

Table_User

• ID: 1, Name: Fred, AddressID: 1 (NULL)
• ID: 2, Name: Joe, AddressID: 2 (1 smith street.....)
• ID: 3, Name: Jane, AddressID: 2 (1 smith street.....)

Table_Address

• ID: 1, Description = NULL
• ID: 2, Description = 1 smith street

etc.

So then i can finally put this all into an indexed view. (my real life scenario has around 8 tables).

NOTE: DB is Microsoft Sql Server 2008, but this could be for any DB.

Q1: Does that design seem ok?

Q2: So what i'm doing here is i'm normalising the data, right? by keeping the inner joins together.

Q3:Lastly, if this is an ok way about it .. can i also make sure the data in the tables are unique (eg. the street addresses) by having some unique constraints or keys or indexes or what (i'm not sure of the proper terminology).

thanks gurus!

Answer 1

I find your question confusing, but maybe I can help a little.

First of all, tables don't have joins, queries have. You don't make a table with a join to another table. There are just 2 tables that may be related, and you can query those tables using joins.

I recommend you to read about db normalization. Wikipedia has a great article: http://en.wikipedia.org/wiki/Database_normalization

About your current case, I'm not sure of what are you trying to do. Having an ID for an address seems ok if that address is repeated in different rows. However, needing several "address tables" seems weird. The most important things to remember when designing are: - Have a correct primary key in every table, so you can correctly join tables. - Do not repeat data unless you have a very very good reason. But I again recommend the previous article.

hope that helps! :)

Answer 2

A very confusing question, so please look up normalization of databases. The 3rd normal form (hopefully it is called like that in english) should solve most problems.

Quick tip: if you have data that is repeated then you need a separate table which you reference in the first one via a foreign key. Everything else is just queries.

Answer 3

So you are basically adding bogus records in tables B and C in order to have the same number of rows as in A? I wouldn't do it if I were you, because if your dataset is large, then you are increasing the number of rows without any real need and also you are running a risk of an inconsistency (your layout heavily depends on your ability to have these bogus records inserted). Apart from that, what do you want to achieve with an indexed view? Performance gain? You are not writing what DBMS you are using, but from my experience in MSSQL this ain't going to give you a lot of gain, because provided that you have proper indexes in tables A, B and C the server will be able to use them to build a good query plan even without an indexed view.

Answer 4

I'd personally say 'no' to this design. Reasons:

• (might not apply) trying to keep the address field normalized implies you need to process this field to make sure you avoid unintended duplicates. I.e. you must make sure the user enters the address in the correct format (otherwise '1 mystreet' could also be entered as '1, mystreet' or whatever - need to check this to avoid duplicates, otherwise normalization is good for nothing)
• even if you find a reason to normalize (i.e. keep separate table for address), the concept of "dummy" address is strange to me. Why not use a nullable FK relationship, i.e. store a NULL address ID in the parent user table, instead of just putting a dummy ID in there.