Clever Geek Handbook

Is this a database design concept OK?

EDIT1: tried to clean up the question by renaming the tables and their relationships. EDIT2: Please don't look at what the TYPE of data I keep in the three DB tables. They were compiled on the fly. They are NOT my real world scripts (and no, I can't talk about my real world data ... in fact it is 1 parent and 6 children, currently). Please just ignore what type of data is and just look at what data is needed. EDIT3: Two FKs are a 0 or 1 to 1 relationship. NOT 0 for many. Not 1 to 1. I am trying to avoid a 0 or 1 to 1 relationship with a 1 to 1 relationship, so I don't need to have EARLY JOINTS, but instead need to LOG IN.

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 couldn't because my tables have outer joins. Sigh. So I was wondering if I could tweak this to look like this:

  • Three tables.
  • table_User has FK in table_Address
  • table_User has FK on table_Vehicle
  • etc..

and tables B and C (which now look like lookup tables).

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

notice the null value? thus something in table_User does not exist in table_Address ... field is null (due to inner join).

Before that I did a LEFT OUTER JOIN, so if there was no data in table_b, I get nulls - the result for each field.

I'll give some sample data here ...

Table_User

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

Table_Address

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

and etc.

So I can finally put the whole thing in an indexed view. (my real life scenario has about 8 tables).

NOTE. DB is Microsoft Sql Server 2008, but it can be for any DB.

Q1: Does this design look ok?

Q2: So what am I doing here, I am normalizing the data, right? keeping the internal connections together.

Q3: In the end, if that's okay ... I can also make sure the data in the tables is unique (like street addresses) by having some unique constraints or keys or indexes or that (I'm not sure about the correct terminology).

thank the guru!

+1


source to share


4 answers


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

First of all, tables do not have joins, queries do. You are not making a table with a join to another table. There are only two tables that can be related, and you can query those tables using joins.

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



About your current case, I'm not sure what you are trying to do. The presence of an identifier for an address appears to be okay if the address is repeated on different lines. However, the need for multiple "address tables" seems odd. The most important things to remember when designing are: - Have the correct primary key in each table so that you can join tables correctly. - Do not repeat data if you have a very good reason. But I recommend the previous article again.

hope this helps! :)

+5


source


Very confusing question, so please check database normalization. The third normal form (hopefully called that in English) should solve most of the problems.



Quick tip: if you have data that is repeating, you need a separate table that you reference in the first one using a foreign key. Everything else is just requests.

+1


source


So you basically add dummy records in tables B and C to have the same number of rows as in A? I wouldn't do this 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 risk inconsistency (your layout is highly dependent on your ability to have these dummy records inserted ). Also, what do you want to achieve with an indexed view? Performance? You do not write which DBMS you use, but from my experience in MSSQL it will not bring you much benefit, because provided that you have the appropriate indexes on tables A, B and C, the server can use to build a good query plan even no indexed view.

0


source


I would personally say no to this design. The reasons:

  • (possibly not applicable) When trying to keep the address field normal, you need to process that field to avoid unintentional duplicates. That is, you have to make sure that the user enters the address in the correct format (otherwise "1 mystreet" could also be entered as "1, mystreet" or something else - you need to check this to avoid duplicates, otherwise normalization means nothing )
  • even if you find a reason for the normalization (ie keep a separate table for the address), the concept of a "dummy" address is strange to me. Why not use the NULL relation FK, i.e. Store the NULL address id in the user's parent table, instead of just putting a dummy id there.
0


source






More articles:


All Articles