Storing Preferences/One-to-One Relationships in Database

What is the best way to store settings for certain objects in my database?

1. Method one: Using a single table

   Table: Company {CompanyID, CompanyName, AutoEmail, AutoEmailAddress, AutoPrint, AutoPrintPrinter}

2. Method two: Using two tables

   Table Company {CompanyID, COmpanyName}

   Table2 CompanySettings{CompanyID, utoEmail, AutoEmailAddress, AutoPrint, AutoPrintPrinter}

   开发者_开发技巧

I would take things a step further...

Table 1 - Company

CompanyID (int)
CompanyName (string)

Example

CompanyID 1
CompanyName "Swift Point"

Table 2 - Contact Types

ContactTypeID (int)
ContactType (string)

Example

ContactTypeID 1
ContactType "AutoEmail"

Table 3 Company Contact

CompanyID (int)
ContactTypeID (int)
Addressing (string)

Example

CompanyID 1
ContactTypeID 1
Addressing "name@address.blah"

This solution gives you extensibility as you won't need to add columns to cope with new contact types in the future.

SELECT
   [company].CompanyID,
   [company].CompanyName,
   [contacttype].ContactTypeID,
   [contacttype].ContactType,
   [companycontact].Addressing
FROM
   [company]
INNER JOIN
   [companycontact] ON [companycontact].CompanyID = [company].CompanyID
INNER JOIN
   [contacttype] ON [contacttype].ContactTypeID = [companycontact].ContactTypeID

This would give you multiple rows for each company. A row for "AutoEmail" a row for "AutoPrint" and maybe in the future a row for "ManualEmail", "AutoFax" or even "AutoTeleport".

Response to HLEM.

Yes, this is indeed the EAV model. It is useful where you want to have an extensible list of attributes with similar data. In this case, varying methods of contact with a string that represents the "address" of the contact.

If you didn't want to use the EAV model, you should next consider relational tables, rather than storing the data in flat tables. This is because this data will almost certainly extend.

Neither EAV model nor the relational model significantly slow queries. Joins are actually very fast, compared with (for example) a sort. Returning a record for a company with all of its associated contact types, or indeed a specific contact type would be very fast. I am working on a financial MS SQL database with millions of rows and similar data models and have no problem returning significant amounts of data in sub-second timings.

In terms of complexity, this isn't the most technical design in terms of database modelling and the concept of joining tables is most definitely below what I would consider to be "intermediate" level database development.

I would consider if you need one or two tables based onthe following criteria:

First are you close the the record storage limit, then two tables definitely.

Second will you usually be querying the information you plan to put inthe second table most of the time you query the first table? Then one table might make more sense. If you usually do not need the extended information, a separate ( and less wide) table should improve performance on the main data queries.

Third, how strong a possibility is it that you will ever need multiple values? If it is one to one nopw, but something like email address or phone number that has a strong possibility of morphing into multiple rows, go ahead and make it a related table. If you know there is no chance or only a small chance, then it is OK to keep it one assuming the table isn't too wide.

EAV tables look like they are nice and will save futue work, but in reality they don't. Genreally if you need to add another type, you need to do future work to adjust quesries etc. Writing a script to add a column takes all of five minutes, the other work will need to be there regarless of the structure. EAV tables are also very hard to query when you don;t know how many records you wil need to pull becasue normally you want them on one line and will get the information by joining to the same table multiple times. This causes performance problmes and locking especially if this table is central to your design. Don't use this method.

It depends if you will ever need more information about a company. If you notice yourself adding fields like companyphonenumber1 companyphonenumber2, etc etc. Then method 2 is better as you would seperate your entities and just reference a company id. If you do not plan to make these changes and you feel that this table will never change then method 1 is fine.

Usually, if you don't have data duplication then a single table is fine.

In your case you don't so the first method is OK.

I use one table if I estimate the data from the "second" table will be used in more than 50% of my queries. Use two tables if I need multiple copies of the data (i.e. multiple phone numbers, email addresses, etc)