What kind of Database structure / data model should I use?

Posted on 2011-05-11
Last Modified: 2012-05-11
My goal is to build a database that stores 3 lists of tags and corresponding data for each tag (each list about 10,000 tags with each tag having 30 fields of data). I have to constantly import these 3 lists every week or so and compare them. Each import will result in unchanged tags, new tags, lost tags, modified tags, and unchanged/new/lost/modified data.

I plan to keep each of these lists in separate tables. Where I need a little help is whether or not I need a 4th table as a “Master List”. I could write a program to verify tags and write the results to the Master List table.

The end result will be a client application that I build that is used to browse the database. The reason I think I need the 4th table (Master List) is to store the results of comparison routines so that the client application doesn’t have to run these processes on the fly.  Also, I’m unsure as to whether I should write duplicate tags to the Master List or to only write unique tags and flag them as having a duplicate in one of the Lists.

Attached are a few tables showing the examples of the data structure. Am I on the correct path? I want to make sure I start this project with a solid foundation...

P.S. I do not have great skills with databases. I can navigate and build queries but that’s about the extent of it. I am relatively proficient with programming.

 database table examples AND end-user report example
Question by:KingMooBot
    LVL 40

    Expert Comment

    You didn't describe your application at all, so noone knows the requirements nor can we tell you if you should do anything. All you've done is described something you decided to do, but not what problem you are solving, so any answer is as good as the next. So if you want more help, describe your requirements.

    I can tell you, though, that when data has the same structure, it usually goes into the same table. 3 tables of the same structure is usually a "red flag" right away.

    Also, if you did have 3 tables of same structure for valid reasons, you can create a view of the tables to represent a master list with UNION

    create view v$masterlist as
    select * from t1
    select * from t2
    select * from t3
    LVL 7

    Expert Comment

    Your idea seems needlessly complex to me, but I could be misunderstanding your requirements.

    From what I gather you need a report of all tags that exist and the status of those tags. This could be generated any one of a number of ways. A master table seems like a reasonable method.

    Perhaps if you give some more detail...
    LVL 23

    Accepted Solution

    - this is what i would suggest:

    1-create 2 table: list_master and list_detail
    2- load all list1, list2 and list3 data into one table called list_detail
    3- in the same list_detail table, create a groupid column to differentiate each tag is belong to which listgroup (1 or 2 or 3) for your Browsing App report purpose.
    4- in the list_master table, i would follow your design as T_masterlist diagram above with additional column for issues. i would also create a column that contain 'week-month-year' data to identify the particular tag is for which week.

    Author Comment

    The reason that I have 3 tables is because the each list's fields are not all identical.

    ex. List1 could have: a,b,c,d
    List2: a, c, d, f, g
    List3: a, g

    From this example the only common fields from all lists is 'a'
    'c' is common between List1 and List 2 only.
    'g' is common between List2 and List 3 only.

    I don't think I should keep all data in one table if each list only utilizes a 1/3rd of the fields.

    The main purpose of my program will be to view issues between common data, highlight changes to data upon subsequent data imports, and allow the user to designate which list has the correct data (ex. if List1_a = 'red' and List2_a = 'blue', if the user decides List1_a is correct then no o matter what data changes occur in the future on List2_a, for that specific tag, List1_a will always be designated as the governing data, unless List1_a no longer 'red' since 'red' was the value that the user originally approved to govern those fields)

    Author Comment


    your comment for an "additional column for issues" on the T_MasterList sounds good. Would this be one field for each of the fields that are common between the 3 Lists?

    List1: a=apple, b=banana, c=carrot
    List2: a=apple, b=blueberry
    List3: a=apple, b=banana, c=candy

    T_MasterList.a_issues = no
    T_MasterList.b_issues = yes
    T_MasterList.c_issues = yes

    Or were you suggesting that I pinpoint the issues?
    LVL 23

    Expert Comment

    hi king,
    - either one also can. you can have the issue column as boolean then add another column called remarks to stated the issues description.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Read about achieving the basic levels of HRIS security in the workplace.
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now