Home » Developer & Programmer » Designer » Data Modelling (Oracle 11G Unix)
Data Modelling [message #609488] Fri, 07 March 2014 06:36 Go to next message
nagaraju.ch
Messages: 103
Registered: July 2007
Location: bangalore
Senior Member
Hi,

I have been given a task to create data model for my organisation's Warehouse, I decided to build Start Schema but when i see Customer Dimension, data comes from several sources. Our aim is to deduplicate the customers. and need to keep some of the fields from each source in warehouse dimension table. If you take Social as source, Customer may come through several social sites Facebook, Twitter, Instagram and etc. need to keep track of each social site as well. Geographical detail come from sales source, Loyalty score comes from CRM system. we have e-mail id as Key in all the systems.

My table would be look like below:
Customerid, name, gender,zipcode, Facebook, Twitter,Instagram,Geographical Score, Loyalty Score and etc.]


Can we split this into several tables and reduce the null values?

Can you please advise on this?
Re: Data Modelling [message #609494 is a reply to message #609488] Fri, 07 March 2014 10:43 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right; create another table and use referential integrity. For example:
-- This table would contain values like 1 - Facebook, 2 - Twitter, etc.
create table source
  (source_id      number(2)    constraint pk_source primary key,
   source_name    varchar2(20) not null
  );
  
create table customer
  (customer_id    number(5)    constraint pk_cust primary key,
   customer_name  varchar2(30) not null,
   gender         varchar2(1),
   --
   -- The following column will store values that exist in the SOURCE table
   source_id      number(2)    constraint fk_cust_source
                                 references source (source_id)
  );
Re: Data Modelling [message #609497 is a reply to message #609494] Fri, 07 March 2014 11:03 Go to previous messageGo to next message
nagaraju.ch
Messages: 103
Registered: July 2007
Location: bangalore
Senior Member
AS i mentioned there may be customer from multiple sources, Eg: customer1 from Facebook and Twitter. In that case Primary key doesn't work right?
Re: Data Modelling [message #609498 is a reply to message #609497] Fri, 07 March 2014 11:06 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As I said, this is just "for example"; I wasn't paying attention to CUSTOMER table primary key - set it to whatever you want (if anything). I just wanted to show how to you

split this into several tables and reduce the null values
Re: Data Modelling [message #642325 is a reply to message #609498] Tue, 08 September 2015 02:44 Go to previous message
nettechindia09
Messages: 3
Registered: August 2015
Junior Member
Data modeling techniques and tools capture and translate complex system designs into easily understood representations of the data flows and processes,
Previous Topic: How to open ER Diagram
Next Topic: Database Design
Goto Forum:
  


Current Time: Thu Nov 21 09:14:50 CST 2024