normalization

Database Normalization. To infinity and beyond?

Exactly how far do you normalize the example below, and exactly which level of normalization does this example meet? CREATE TABLE "public"."contact_info" ( "id" SERIAL, "home_phone" TEXT, "mobile_phone" TEXT, "work_phone" TEXT, "fax_phone" TEXT, "email" TEXT, "line1" TEXT, "line2" TEXT, "city" TEXT, "state_i...

String Anglicization?

Is anyone aware of any simple way to anglicize a string? Currently, in our system, we're doing replacements on "invalid" characters, such as shown below: ret = ret.Replace("ä", "ae"); ret = ret.Replace("Ä", "Ae"); ret = ret.Replace("ß", "ss"); ret = ret.Replace("ç", "c"); ret = ret.Replace("Ç", "C...

Normalizing URI to make it work correctly with MakeRelativeUri

Dim x AS New URI("http://www.example.com/test//test.asp") Dim rel AS New URI("http://www.example.com/xxx/xxx.asp") Console.Writeline(x.MakeRelativeUri(rel).Tostring()) In here output is: ../../xxx/xxx.asp Which looks correct almost all web servers will process the two of the following as same request: http://www.example.com/test//t...

SQL Query :Filter help needed

I have 2 tables.One is OrderMaster and second is OrderDetails.Both are connected via OrderId Order table fields : OrderId(Primary Key),Total,OrderDate OrderDetail fields : OrderDetailId,ItemId,SupplierId,Amount,OrderId (ForiegnKey) One order can have multiple orderdetail records which can be from various suppliers Now i want to get O...

How to represent a set of entities from separate tables?

I have a few tables representing geographical entities (CITIES, COUNTIES, ZIPCODES, STATES, COUNTRIES, etc). I need to way represent sets of geographical entities. A set can contain records from more than one table. For example, one set may contain 3 records from CITIES, 1 record from COUNTIES and 4 from COUNTRIES. Here are two possi...

How do I list normalised data from MySQL in PHP?

I always struggle with dealing with normalised data, and how I display it. Maybe its because I don't fully understand the normalisation rules, like how to get it fully into Boyce-Codd. Performance is not really an issue at this stage, though maintainability of the schema is. user ID Name 1 Alice 2 Bob 3 Charlie skills ID ...

Using SQLite or MS Access, how should the tables be organized to model the logic of this 3D structure?

Here is an explanation of how the elements are related. tl;dr: Intensity is strongest at the center of the diagram. The basic emotions (in the arms) combine to form secondary emotions in the outer circle. (i.e., Anticipation + Joy = Optimism) What is the best way to design a database to model this set of relationships? ...

To normalize or not to normalize user_ids

In my Rails application, I have a variety of database tables that contain user data. Some of these tables have a lot of rows (as many as 500,000 rows per user in some cases) and are queried frequently. Whenever I query any table for anything, the user_id of the current user is somewhere in the query - either directly, if the table has ...

MYSQL / PHP - Efficient method for attaching users to a project

I'm building a system that has a user table and a project table. I'm trying to determine the most efficient way to connect users to a project. I'm was thinking about a table that records the relationship, but I wasn't sure if a serialized list in one field would work just as well. ...

Is there an answer matrix I can use to decide if I need a foreign key or not?

For example, I have a table that stores classes, and a table that stores class_attributes. class_attributes has a class_attribute_id and a class_id, while classes has a class_id. I'd guess if a dataset is "a solely child of" or "belongs solely to" or "is solely owned by", then I need a FK to identify the parent. Without class_id in the ...

How would I optimize this database design?

I am creating an application which generates statistical information for a pupil's performance over their school career. To achieve this, I need to store every grade ever attained by a pupil. But I have no idea how to optimally store this information. This is a preliminary design, but don't know how it will hold up in practice. So ea...

Is there a library that does what the Levelator does for .Net?

The Levelator is a program that you feed an audio file and it generates another one with a more constant volume ensuring that any recording problems (like a person sounding too loud, or being barely audible) are corrected. Do you know any libraries that I could use .Net in Windows to perform the same task? Or a command-line program woul...

Combine contents of multiple rows in 3NF mysql tables

Having dutifully normalised all my data, I'm having a problem combining 3NF rows into a single row for output. Up until now I've been doing this with server-side coding, but for various reasons I now need to select all rows related to another row, and combine them in a single row, all in MySQL... So to try and explain: I have three tab...

Is it really worth it to normalize the "Toxi" way? ( 3NF )

I'm in the early stages of my database design so nothing is final yet, and I'm using the "TOXI" 3-table design for my threads which have optional tags, but I can't help but feel that the joining is not really necessary and perhaps I need to just rely on a simple tags column in my posts table where I can just store a varchar of something ...

When is a good time to break normalization rules?

Please give your opinion of situations in which it is a good idea to NOT normalize. I just witnessed some heated discussions between an architech and DBA who insisted in which one was arguing the database was TOO normalized. ...

Normalization: Two coloumns in a single row require data from the same table.

I have a table which has the following fields: Assignedto and completedby These fields reference the employee table. I was just wondering if this is normalized or whether I have missed something. Thanks ...

Normalization Help

I am refactoring an old Oracle 10g schema to try to introduce some normalization. In one of the larger tables, there is a text field that has at most, 10-15 possible values. In my mind, it seems that this field is an example of unnecessary data duplication and should be extracted to a separate table. After examining the data, I cannot...

Need some input on MySQL schema

I need some inputs on how I should structure my db. Let's say I want to store members data in members table. There are two types of members, let's say person and organization. Person and organization have different set of information so that we have to store them into two different tables: let's say persons and organizations. I would h...

Is there a normalized form for representing several objects from different tables?

For a system I am currently building, the following two scenarios occur: My permissions system is the perennial favorite of attaching permissions to roles, and roles to users, but with a twist: permissions may be applied to any asset at any tier, and there are 4 "Tiers", numbered 0 through 3. As such, the role assignment table is 5 fi...

Normalizing Data on a Table

I need help to transform data on a table, reducing a series of columns to one single column. An example follows below: Frequency_1 integer, Frequency_2 integer, Frequency_3 integer, Frequency_4 integer, These columns currently hold 1 or 0. Only one column will hold 1. The new column should be defined as Frequency integer And this ...