Duplicate data

[Rob Green]

I am trying to maintain different state for filenames, so i have a table
with the list of filenames, then in the main table i would link the filename
field to it. However this looks like it is duplicating the actual filename
for each record (i have 100,000's of them so this is bad). I have set up
one-to-many relationship and have enforce relationship integrity enabled.
How can i prevent the filename being duplicated per record?

Main table
ID Autonumber (primary key)
FileName Text - related to FileName.Name
Action Text

Filename table
Name Text (Primary key) - related to Main.FileName


Also, i am using ODBC to actually insert the record in the database like so
CHECK(retcode = SQLBindParameter(
hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_WCHAR,
SQL_WCHAR,
255,
0,
(TCHAR*)FileName,
sizeof(FileName),
&FileNameInd));

"INSERT INTO Main (FileName, Action) VALUES (?, ?)"


I thought about adding a ID field to Filename table but that seems overkill
considering the name is unique and is the primary key.

 

[Ken Snell [MVP]]

What you describe as your setup sounds perfectly acceptable to me. What
you're doing by putting the state name in a different table is avoiding the
need to change 100,000s of records when you find a typo in a state name. You
just change it once in the state name table, and all other records change at
the same time. Don't change a thing! < g >

 

[Rob Green]

It is duplicating the data. if i add 1000 entries to the database, then
look at it in a hex viewer, i see 1001 copies of the filename. This is what
i am trying to prevent. Could it be that i am using a text field for the
primary key in the FileName table? I am not really worried about changing
the name due to a type, but more so that i will have a 100 entries for the
same filename. However i did try changing an entry in the filename table
and got the following error 'This record cannot be deleted or changed
because table 'main' includes related records'. Maybe i have the database
set up incorrectly?


I couldnt find how to export the SQL statement for making the database but
here is the main.xsd

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema"
xmlnsd="urn:schemas-microsoft-comfficedata">
<xsd:element name="dataroot">
<xsd:complexType>
<xsd:choice maxOccurs="unbounded">
<xsd:element ref="main"/>
</xsd:choice>
</xsd:complexType>
</xsd:element>
<xsd:element name="main">
<xsd:annotation>
<xsd:appinfo>
<od:index index-name="PrimaryKey" index-key="ID " primary="yes" unique="yes"
clustered="no"/>
<od:index index-name="Action" index-key="Action " primary="no" unique="no"
clustered="no"/>
<od:index index-name="Actionmain" index-key="Action " primary="no"
unique="no" clustered="no"/>
<od:index index-name="ID" index-key="ID " primary="no" unique="no"
clustered="no"/>
<od:index index-name="LongFileNamemain" index-key="LongName " primary="no"
unique="no" clustered="no"/>
<od:index index-name="LongName" index-key="LongName " primary="no"
unique="no" clustered="no"/>
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ID" od:jetType="autonumber" od:sqlSType="int"
od:autoUnique="yes" od:nonNullable="yes">
<xsd:simpleType>
<xsd:restriction base="xsd:integer"/>
</xsd:simpleType>
</xsd:element>
<xsd:element name="LongName" minOccurs="0" od:jetType="text"
od:sqlSType="nvarchar">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="255"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

and here is the filename table
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema"
xmlnsd="urn:schemas-microsoft-comfficedata">
<xsd:element name="dataroot">
<xsd:complexType>
<xsd:choice maxOccurs="unbounded">
<xsd:element ref="LongFileName"/>
</xsd:choice>
</xsd:complexType>
</xsd:element>
<xsd:element name="LongFileName">
<xsd:annotation>
<xsd:appinfo>
<od:index index-name="PrimaryKey" index-key="Name " primary="yes"
unique="yes" clustered="no"/>
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Name" minOccurs="0" od:jetType="text"
od:sqlSType="nvarchar">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="255"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

 

[Ken Snell [MVP]]

Sounds as if you have the filename table set up as a child of the main
table. It should be reversed. Or you don't have Cascade Update checked for
the referntial integrity.

The filename table should have the file name field be set as a primary key
field. It can be a text field; no problem.

The main table should have the file name field set with the same data type
and text length as the file name field in the filename table. It can be set
to Indexed: Yes (Duplicates OK) if you wish. Be sure to not set it to "No
Duplicates".

Then open the relationship window. If not already there, add the two tables:
filename and main. Click on the file name field in the filename table; and
drag it to the file name field in the main table. A window will show, and
you want to require referntial integrity. The master field should be the
file name field of the filename table, and the child field should be the
file name field in the main table. Also check the Cascade Update Related
Fields box.

Click OK in this referential integrity window. You now should see a 1 on the
file name field of the filename table; and an infinity symbol on the file
name field of the main table.

This then means that you can use any file name value from the filename table
in the main table, and if you change the name in the filename table then the
names will change in the main table with no additional work by you.

If you're concerned about storing the file name text in so many records,
then add an autonumber field to the file name table, set it as the primary
key field, and use that primary key value in the main table in place of the
file name text. You'll need to delete the relationship in the Relationship
window first, and then be sure to change the type of field in the main table
to be a Long Integer field, and then you'll need to reset the relationships.